The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
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
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
80 | |
79 | |
47 | |
39 |
User | Count |
---|---|
149 | |
110 | |
66 | |
64 | |
56 |