Cumulative number of entries in dates range

I'm trying to get cumulative number of unique IDs in given timeframe. My DAX look like this:

Cumulative = CALCULATE(SUM(Data[ID]));DATESBETWEEN(Data[ack_date];DATE(YEAR(NOW());4;1);DATE(YEAR(NOW());11;30)))

There is similar measure for Year-1: [YEAR(NOW())-1]

What I want to achieve is area chart showing growing number od IDs in time comparing same periods this and previous year. When I give those measures as Values for chart and "ack_date" as its Axis what I get is values comparison month by month but not cumulative, just value for certain month.

1 answer

  • answered 2017-10-11 10:05 Kresimir L.

    Try this code. Adjust for year -1.

    =
    CALCULATE (
        DISTINCTCOUNT ( Data[ID] ),
        FILTER (
            ALL ( Data ),
            AND (
                Data[Ack_date] <= MAX ( Data[Ack_date] ),
                AND (
                    Data[Ack_date] <= DATE ( YEAR ( NOW () ), 11, 30 ),
                    Data[Ack_date] >= DATE ( YEAR ( NOW () ), 4, 1 )
                )
            )
        )
    )
    

    enter image description here