Calculating Values Between Snapshot Dates

[Ayric2]

Hello,

I am fairly new to BI and have been trying to calculate values between historical snapshot dates (day to day). Ideally, I'd like the target data to be further summarized week-to-week, month-to-month, quarter-to-quarter, and year-to-year.

Source Data example:

Snapshot Date	Item ID	Item Count
1/1/2000	1000	100
1/1/2000	2000	200
1/1/2000	3000	300
1/1/2000	4000	400
1/2/2000	2000	100
1/2/2000	3000	400
1/2/2000	5000	100
1/3/2000	2000	200
1/3/2000	3000	400
1/3/2000	5000	200
1/3/2000	6000	50
1/3/2000	7000	1000

Target Data / Values

Snapshot Date	# Items	# Items Added	# Items Removed	Total Count	Diff Count
1/1/2000	5	n/a	n/a	1000	n/a
1/2/2000	3	1	2	600	-400
1/3/2000	4	2	0	1850	1150

Summarizing the # Items and Total Count per day is straightforward, but figuring out how many unique items are added and removed each day and the difference in total count each day has been difficult.

I really appreciate your thoughts and help with this! Thank you!

(Apologies for any formatting issues and incorrect manual math...)

[Icey · Accepted solution]

Hi @Ayric2 ,

Please check:

Items Added = 
VAR MinDate_ =
    MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] )
VAR ThisDay_ =
    MAX ( 'Table'[Snapshot Date] )
VAR PreviousDay_ =
    CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ )
VAR ThisDayItems_ =
    DISTINCT ( 'Table'[Item ID] )
VAR PreviousDayItems_ =
    DISTINCT (
        SUMMARIZE (
            FILTER ( ALL ( 'Table' ), 'Table'[Snapshot Date] = PreviousDay_ ),
            [Item ID]
        )
    )
VAR SameItems_ =
    DISTINCT ( INTERSECT ( ThisDayItems_, PreviousDayItems_ ) )
RETURN
    IF (
        MAX ( 'Table'[Snapshot Date] ) = MinDate_,
        "N/A",
        COUNTROWS ( ThisDayItems_ ) - COUNTROWS ( SameItems_ )
    )

Items Removed = 
VAR MinDate_ =
    MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] )
VAR ThisDay_ =
    MAX ( 'Table'[Snapshot Date] )
VAR PreviousDay_ =
    CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ )
VAR ThisDayItems_ =
    DISTINCT ( 'Table'[Item ID] )
VAR PreviousDayItems_ =
    DISTINCT (
        SUMMARIZE (
            FILTER ( ALL ( 'Table' ), 'Table'[Snapshot Date] = PreviousDay_ ),
            [Item ID]
        )
    )
VAR SameItems_ =
    DISTINCT ( INTERSECT ( ThisDayItems_, PreviousDayItems_ ) )
RETURN
    IF (
        MAX ( 'Table'[Snapshot Date] ) = MinDate_,
        "N/A",
        COUNTROWS ( PreviousDayItems_ ) - COUNTROWS ( SameItems_ )
    )

Diff Count = 
VAR MinDate_ =
    MINX ( ALLSELECTED ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] )
VAR ThisDay_ =
    MAX ( 'Table'[Snapshot Date] )
VAR PreviousDay_ =
    CALCULATE ( MAX ( 'Table'[Snapshot Date] ), 'Table'[Snapshot Date] < ThisDay_ )
VAR ThisDayCount_ =
    SUM ( 'Table'[Item Count] )
VAR PreviousDayCount_ =
    CALCULATE ( SUM ( 'Table'[Item Count] ), 'Table'[Snapshot Date] = PreviousDay_ )
RETURN
    IF (
        MAX ( 'Table'[Snapshot Date] ) = MinDate_,
        "N/A",
        ThisDayCount_ - PreviousDayCount_
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ayric2 , if you need end of week , month and qtr, year

Check video and functions https://www.youtube.com/watch?v=yPQ9UV37LOU closingbalancemonth, closingbalancequarter, closingbalanceyear

For Week you have to compare date with the end of week date

Have this in your date tbale

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)

calculate(sum(Table[tem Count]), Filter(Date, Date[DAte] = Date[Week end Date]))

TO run qtr, year , month  week refer

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.