Forum Discussion

Ayric2's avatar
Ayric2
Microsoft Employee
5 years ago
Solved

Calculating Values Between Snapshot Dates

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 DateItem IDItem Count
1/1/20001000100
1/1/20002000200
1/1/20003000300
1/1/20004000400
1/2/20002000100
1/2/20003000400
1/2/20005000100
1/3/20002000200
1/3/20003000400
1/3/20005000200
1/3/2000600050
1/3/200070001000

 

Target Data / Values

Snapshot Date # Items # Items Added# Items RemovedTotal CountDiff Count
1/1/20005n/an/a 1000n/a
1/2/200031 2 600-400
1/3/200042018501150

 

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...)

Need Help
  • Icey's avatar
    Icey
    5 years ago

    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.

2 Replies