Count function of previous date record

[Anonymous]

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!

[Icey · Accepted solution]

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.