Forum Discussion

Ayric2's avatar
Ayric2
Icon for Microsoft Employee rankMicrosoft 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, mont...
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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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-Last-Week/ba-p/1051123
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-5c3243d1f9

 

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.