Forum Discussion

jh292's avatar
jh292
Icon for Helper I rankHelper I
5 years ago
Solved

Calculating change in data between two report dates within the same sheet

Hi   New to powerBI, hopefully a quick fix.    I have a table that I need to make a messure for (I think) to show me the change over a period of time. Ideally that I can adapt / copy paste so I c...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi jh292 

    I build a table like yours to have a test.

    To calculate insight gathered in the last 2 days, firstly calculate the Maxdate1(2020/10/13) within last 2 days and then calculate the Maxdate2 which is before Maxdate1(2020/10/11).

    Measure:

    Measure = 
VAR _Max2Days =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            DATEDIFF ( 'Table'[Report Date], TODAY (), DAY ) <= 2
        ),
        'Table'[Report Date]
    )
VAR _MaxMinus =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Report Date] < _Max2Days ),
        'Table'[Report Date]
    )
VAR _SumA =
    SUMX (
        FILTER ( 'Table', 'Table'[Report Date] = _Max2Days ),
        'Table'[Known Insight]
    )
VAR _SumB =
    SUMX (
        FILTER ( 'Table', 'Table'[Report Date] = _MaxMinus ),
        'Table'[Known Insight]
    )
RETURN
    _SumA - _SumB

     Result is A: 6-5=1,B:6-6=0,C:5-4=1 sum=2.

    You can download the pbix file from this link: Calculating change in data between two report dates within the same sheet

     

    Best Regards,

    Rico Zhou

     

    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

jh292 , Not very clear, but with date table, you can try

 

Rolling 30= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],staroffmonth(Sales[Sales Date]),-30,day))

Rolling 30 to 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-30,day)),-30,day))

 

Rolling 3= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],staroffmonth(Sales[Sales Date]),-3,day))

Rolling 3 to 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-3,day)),-3,day))

 

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.

  • jh292's avatar
    jh292
    Icon for Helper I rankHelper I
    5 years ago

    Affraid that still just totals everything regardless of date.... 

    Thanks for trying!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi jh292 

      I build a table like yours to have a test.

      To calculate insight gathered in the last 2 days, firstly calculate the Maxdate1(2020/10/13) within last 2 days and then calculate the Maxdate2 which is before Maxdate1(2020/10/11).

      Measure:

      Measure = 
VAR _Max2Days =
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            DATEDIFF ( 'Table'[Report Date], TODAY (), DAY ) <= 2
        ),
        'Table'[Report Date]
    )
VAR _MaxMinus =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Report Date] < _Max2Days ),
        'Table'[Report Date]
    )
VAR _SumA =
    SUMX (
        FILTER ( 'Table', 'Table'[Report Date] = _Max2Days ),
        'Table'[Known Insight]
    )
VAR _SumB =
    SUMX (
        FILTER ( 'Table', 'Table'[Report Date] = _MaxMinus ),
        'Table'[Known Insight]
    )
RETURN
    _SumA - _SumB

       Result is A: 6-5=1,B:6-6=0,C:5-4=1 sum=2.

      You can download the pbix file from this link: Calculating change in data between two report dates within the same sheet

       

      Best Regards,

      Rico Zhou

       

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