Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count function of previous date record

Hi,

 

I want to present the 'count of item number' of the previous report date and show the % difference. So for report date 06/12/2020 the record should be blank and for report date 13/12/2020 the new record should show 19, so that I am able to calculate the difference. In this case (14-19)/19 = -26%. What column based calculation and/or measure can I implement? I am working on direct query!

 

Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi Anonymous ,

     

    Create a measure like so:

    Measure =
VAR ThisDate =
    MAX ( 'Table'[Report Date] )
VAR PreDate =
    CALCULATE ( MAX ( 'Table'[Report Date] ), 'Table'[Report Date] < ThisDate )
VAR PreCount =
    CALCULATE ( COUNT ( 'Table'[Item Number] ), 'Table'[Report Date] = PreDate )
VAR ThisCount =
    COUNT ( 'Table'[Item Number] )
RETURN
    DIVIDE ( ThisCount - PreCount, PreCount )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.

1 Reply

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Create a measure like so:

    Measure =
VAR ThisDate =
    MAX ( 'Table'[Report Date] )
VAR PreDate =
    CALCULATE ( MAX ( 'Table'[Report Date] ), 'Table'[Report Date] < ThisDate )
VAR PreCount =
    CALCULATE ( COUNT ( 'Table'[Item Number] ), 'Table'[Report Date] = PreDate )
VAR ThisCount =
    COUNT ( 'Table'[Item Number] )
RETURN
    DIVIDE ( ThisCount - PreCount, PreCount )

     

     

    Best regards

    Icey

     

    If this post helps, then consider Accepting it as the solution to help other members find it faster.