Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Difference between last row

DateCOUNT(ID)
01/01/202315000
05/02/202315100
07/02/202315030
08/02/202318000
17/03/202314500


Hello there,

I use direct query, how can I calculate change between days without any logical chronology between each day?
I just want to calculate the change with the previous row.

Thanks in advance,

  • Anonymous's avatar
    Anonymous
    3 years ago

    Thanks a lot for your help barritown. I found the right code :

    AMeasure Difference =
VAR CurrentCount = CALCULATE(COUNT(products[id]))
VAR CurrentDate = LASTDATE(products[date])
VAR PreviousDate = MAXX(FILTER(ALL(products), products[date] < CurrentDate ), products[date])
VAR PreviousCount = CALCULATE(COUNT(products[id]), FILTER(ALL(products), products[date] = PreviousDate))
RETURN IF(ISBLANK(PreviousCount), BLANK(), CurrentCount - PreviousCount)

    This looks like OK for me 🙂

4 Replies

  • barritown's avatar
    barritown
    Solution Sage
    3 years ago

    Hi Anonymous,

    If entries in your table are sorted by date and there are no duplicate dates, you could try such a measure:

    In plain text for convenience:

    Difference Measure = 
VAR CurrentCount = MAX ( [COUNT(ID)] )
VAR CurrentDate = MAX ( [Date] )
VAR PreviousDate = MAXX ( FILTER ( ALL ( data ), [Date] < CurrentDate ), [Date] )
VAR PreviousCount = MAXX ( FILTER ( ALL ( data ), [Date] = PreviousDate ), [COUNT(ID)] )
RETURN IF ( ISBLANK ( PreviousCount ), BLANK(), CurrentCount - PreviousCount )

    Best Regards,

    Alexander

    My YouTube vlog in English

    My YouTube vlog in Russian

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hello barritown, thanks a lot for your help.
    Your purpose doesn't work because COUNT(ID) is a calculated measure already (Measure COUNT(ID) = Count(products[ID])
    I've tried to set a column for this count but it doesn't work too.

     

    AMeasure Difference = 
VAR CurrentCount = MAX(COUNT(products[id]))
VAR CurrentDate = MAX(products[date]) 
VAR PreviousDate = MAXX(FILTER(ALL(products), [date] < CurrentDate ),[date]) 
VAR PreviousCount = MAXX(FILTER(ALL(products), [date] = PreviousDate ), (COUNT(products[id]))) 
RETURN IF(ISBLANK(PreviousCount), BLANK(), CurrentCount - PreviousCount)

    Error : The MAX function in DAX only accepts a column reference as an argument

     

     



     

    • barritown's avatar
      barritown
      Solution Sage
      3 years ago

      Hello Anonymous, 

      For me it's difficult to take another try w/o access to your data model. If you can create a PBIX containing it and some toy data and share it with me, I'll be able to try to adapt my solution for your model. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks a lot for your help barritown. I found the right code :

        AMeasure Difference =
VAR CurrentCount = CALCULATE(COUNT(products[id]))
VAR CurrentDate = LASTDATE(products[date])
VAR PreviousDate = MAXX(FILTER(ALL(products), products[date] < CurrentDate ), products[date])
VAR PreviousCount = CALCULATE(COUNT(products[id]), FILTER(ALL(products), products[date] = PreviousDate))
RETURN IF(ISBLANK(PreviousCount), BLANK(), CurrentCount - PreviousCount)

        This looks like OK for me 🙂