Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Georgetimes
Frequent Visitor

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

 

 

 

 

Georgetimes_1-1706260041655.png

 

Customerbooking datebroker
A01/01/20231
A02/01/20231
A03/01/20231
A04/01/20231
A05/01/20231
A06/01/20231
A07/01/20231
A08/01/20232
A09/01/20232
A10/01/20232
A01/02/20231
A02/02/20231
A03/02/20231
A04/02/20231
A05/02/20231
A06/02/20231
A07/02/20231
1 REPLY 1
DataInsights
Super User
Super User

@Georgetimes,

 

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

 

DataInsights_0-1706281570789.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.