Forum Discussion

markus_zhang's avatar
markus_zhang
Icon for Advocate III rankAdvocate III
7 years ago
Solved

DAX: Subtract previous row from current row

Hi Experts, This must be done in a measure, not in a calculated column. I'm working in Excel 2016 not Power BI. I have a sales table qryDump with the following content: (Note there is gap between d...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    Oh yeah, should have explained that. Yes you can use EARLIER in measures, you would do something like this:

     

    Measure = 
VAR __index = MAX([Index]) //in visual table context, this is the current index
VAR __table = 'Table'
VAR __table1 = ADDCOLUMNS(__table,"__diff",[Impressions] - MAXX(FILTER(ALL('Table'),[Index]=EARLIER([Index])-1),[Impressions])
RETURN
MAXX(FILTER(__table1,[Index]=__index),[__diff])

    Something like that, I didn't test it for syntax erros.

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi markus_zhang,

 

Please refer to below measure:

Result =
VAR currentindex =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Impressions[Date] ),
            MONTH ( Impressions[Date] ) = MONTH ( MAX ( Impressions[Date] ) )
                && Impressions[Date] <= MAX ( Impressions[Date] )
        )
    )
VAR previouImpressions =
    CALCULATE (
        SUM ( Impressions[Impressions] ),
        FILTER (
            ALLSELECTED ( Impressions ),
            [Index]
                = currentindex - 1
                && MONTH ( Impressions[Date] ) = MONTH ( MAX ( Impressions[Date] ) )
        )
    )
RETURN
    SUM ( Impressions[Impressions] ) - previouImpressions

Best regards,

Yuliana Gu