Forum Discussion
Difference Sum between two dates - variable date
Hi everyone, needs your help with the below
Don't know how I can attach a PBI, however please find two screenshots, one with a demo data and one with how this should look like.
The easiest I can explain what I want is that I'd like to see the difference in bookings between period A and period B and I'd like to filter it by brokers, however there's no fixed or preestablished period A and B and that's why I'll need a filter where I can select the period A (I.e 01/01/2023 - 03/01/2023) and period B (I.e 05/01/2023 - 10/01/2023)
For example:
There're 10 bookings in Jan 23 for example, all for same customer, however diff brokers.
What I want for example is select first period (period A) - All Jan - and select period B as all Feb
All Jan - 10 bookings
All Feb - 7 bookings
so I want in the table to see
Customer Bookings
A -3 ( which is 7 - 10)
This will go a bit further as I also need a broker filter. I would also want to check the above numbers based on broker:
if I choose broker 1:
- all Jan - 7 bookings (period A) for broker 1
- all Feb - 7 bookings (period B) for broker 1
So the table will now show
Customer bookings
A 0 (7 - 7)
If I go further and amend the dates for period A (01/01/2023 - 01/01/2023) and period B (03/01/2023 - 04/01/2023) for broker 1 this will mean:
period A - 1 booking
period B - 2 bookings
The table will now show
Customer Booking
A 1 (which is 2 - 1)
Hope this makes sense and sorry about the long explanation
| Customer | booking date | broker |
| A | 01/01/2023 | 1 |
| A | 02/01/2023 | 1 |
| A | 03/01/2023 | 1 |
| A | 04/01/2023 | 1 |
| A | 05/01/2023 | 1 |
| A | 06/01/2023 | 1 |
| A | 07/01/2023 | 1 |
| A | 08/01/2023 | 2 |
| A | 09/01/2023 | 2 |
| A | 10/01/2023 | 2 |
| A | 01/02/2023 | 1 |
| A | 02/02/2023 | 1 |
| A | 03/02/2023 | 1 |
| A | 04/02/2023 | 1 |
| A | 05/02/2023 | 1 |
| A | 06/02/2023 | 1 |
| A | 07/02/2023 | 1 |
1 Reply
Try this measure. It requires two date tables (PeriodA, PeriodB) that do not have any relationships. Create each slicer using its corresponding date table. TREATAS changes the lineage of the date tables to the date in Bookings.
Bookings = VAR vCountPeriodA = CALCULATE ( COUNTROWS ( Bookings ), TREATAS ( VALUES ( PeriodA[Date] ), Bookings[booking date] ) ) VAR vCountPeriodB = CALCULATE ( COUNTROWS ( Bookings ), TREATAS ( VALUES ( PeriodB[Date] ), Bookings[booking date] ) ) VAR vResult = vCountPeriodB - vCountPeriodA RETURN vResult
