Calculate quantity of different days between two times

(0) Share

Report

Posted on by rgparisoto

220

Hi guys!

I'm trouble here with a client necessity.

He has an hotel and has two kind of prices to rent a room, one between monday to thursday, and another to friday do sunday.

How can I calculate the total price to him?

Example:

- The client arrives at hotel on wednesday and leave at monday, using the logic I'll have:

-- Wed and thu as normal days (R$ 100,00 per day);

-- Fri, sat and sun as more expensive days (R$ 150,00 per day).

Then, I have R$ 200,00 + R$ 450,00 = R$ 650,00.

Is it possyble to do with some formula?

Thank you.

Regards,

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Mike8 1,330 on at

Like (1)

Report

Hello,

If you share some more information maybe it's easier but either way here is a sample app..

You will need 2 datepickers so you can select the start and the end date.. DatePicker1 and DatePicker2
________________
One collection that you will use in a ForAll expression
(as you can see it has 30 records. That means if a guest stays more than 30 days the formula will not work. You can add records in the collection to cover long periods.)

Button1.OnSelect= ClearCollect(DummyCollectionToIterate,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)
_______________
Button2 creates a collection with the number of records equal to the difference between the selected dates,
it checks the day one by one between the selected dates (weekday returns a number for example 1 and not the actual day Monday) and at the end if its Monday to Thursday (1 to 4) adds 100 in the collection, else 150

Button2.OnSelect=
Clear(rgparisotoCollection);
ForAll(Filter(DummyCollectionToIterate,Value<=DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate)),
Collect(rgparisotoCollection,
If(Weekday(DateAdd(DatePicker1.SelectedDate,Value-1,Days),StartOfWeek.Monday)<5,100,150)
))

Label1.Text = Sum(rgparisotoCollection,Value) <--- I think, this is what you asked for.

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report
Hi @rgparisoto,

I agree with @Mike8's thought almost. You should add a Two Date Picker controls within your app, represents the Arrive Date and Leave Date.

I have made a test on my side, please take a try with the following workaround:

Set the OnVisible property of the screen to following formula:

ClearCollect(DurationDays,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30)

Set the OnSelect property of the Button control (CalculateTotalPrice) to following formula:

Clear(PriceCollection);
ForAll(
Filter(DurationDays,Value<=DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate)),
Collect(PriceCollection,If(Weekday(DateAdd(DatePicker1.SelectedDate,Value-1,Days),StartOfWeek.Monday)<5,100,150))
)

Set the Text property of the Label control to following formula:

Sum(PriceCollection,Value)

More details about the DateAdd, DateDiff and Weekday function in PowerApps, please check the following article:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datetime-parts

Best regards,

Kris

Was this reply helpful? Yes No
KroonOfficeSol 587 on at

Like (0)

Report
Probably you would want to change the prices for a room on specific days at another place in you app. So you get:
A list off Rooms
A list off Prices (with columns: Room, Day, Prices)
Both will be collected/connected to a other table in your datasource.

So you will add a combobox to pick a specific room, two calender controls to pick the date and a button to run the collections. Formula becauses slightly different like:
Clear(PriceCollection); ForAll( Filter(DurationDays,Value<=DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate)), Collect(PriceCollection,LookUp(PricesList, DayNo = Weekday(DateAdd(DatePicker1.SelectedDate,Value-1,Days),StartOfWeek.Monday) && Room = ComboBox1.Selected.Value, Prices)) )
Like in this pictures:

Here the formula's for creating the dummy data:
ClearCollect(Rooms,"Room 1", "Room 2")
ClearCollect(PricesList, {Room:"Room 1", DayNo:1,Prices:100}, {Room:"Room 1", DayNo:2,Prices:100}, {Room:"Room 1", DayNo:3,Prices:100}, {Room:"Room 1", DayNo:4,Prices:120}, {Room:"Room 1", DayNo:5,Prices:100}, {Room:"Room 1", DayNo:6,Prices:150}, {Room:"Room 1", DayNo:7,Prices:150}, {Room:"Room 2", DayNo:1,Prices:80}, {Room:"Room 2", DayNo:2,Prices:80}, {Room:"Room 2", DayNo:3,Prices:80}, {Room:"Room 2", DayNo:4,Prices:80}, {Room:"Room 2", DayNo:5,Prices:80}, {Room:"Room 2", DayNo:6,Prices:95}, {Room:"Room 2", DayNo:7,Prices:95} )
Hope this helps.

Was this reply helpful? Yes No
rgparisoto 220 on at

Like (0)

Report

Thank you very much guys!

I've been use your idea and it works perfecty, the only change I did was add a day at datediff:
ForAll(Filter(DummyCollectionToIterate,Value<=DateDiff(DatePicker1.SelectedDate,DatePicker2.SelectedDate + 1)),

Everything is working so good, you're the best!

Regards,

Robson, from Brazil.

Was this reply helpful? Yes No
v-xida-msft on at

Like (0)

Report

Hi @rgparisoto,

Have you solved your problem?

If you have solved your problem, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify.

Best regards,

Kris

Was this reply helpful? Yes No