Measure to calculate total count of rows from preceding months

[Muzaffar_Ali]

I have a dataset that populates a new row every year for each unique ID. I want to count the number of rows for the preceding 12 months from the current month being viewed in a matrix table. So the Column field is populated with "Date" and these dates are the 1st of every month:

To do this I created a measure with a variable identifying what the oldest month would be:

VAR StartofCalc =

        CALCULATE( LASTNONBLANK('Data'[DATE_MONTH],1), DATEADD( 'Data'[DATE_MONTH], -13, MONTH ) )

and another variable showing the date of the previous month

VAR EndofCalc =

        CALCULATE( LASTNONBLANK('Data'[DATE_MONTH],1), DATEADD( 'Data'[DATE_MONTH], -1, MONTH ) )

then I returned the calculation I am trying to perform, which is to get the total number of rows between the above two periods:

RETURN

    CALCULATE (

            COUNT( 'Data'[ID_NBR]),

            ALLEXCEPT('Data', 'Data'[DATE_MONTH]),

            FILTER (

                'Data',

                'Data'[DATE_MONTH] >= DATEVALUE(StartofCalc) &&

                'Data'[DATE_MONTH] <= DATEVALUE(EndofCalc) &&

                'Data'[Status] = "Active"

            ))

I also applied the "ALLEXCEPT" funtion as there is a slicer filter being applied to this table filtering the 'Data'[DATE_MONTH] to return the period we want to look at.

This is not returning any values, and if I remove the "'Data'[DATE_MONTH] <= DATEVALUE(EndofCalc) &&" part of the measure we start to see figures, but these are only the figures for the month we are looking at, not the total rows for the preceding 12 months.

To note, we only have 18 months of data

[Anonymous]

HI @Muzaffar_Ali,

You can try to use the following measure formula if this suitable for your requirement:

formula =
VAR currDate =
    MAX ( 'Data'[DATE_MONTH] )
VAR StartofCalc =
    CALCULATE (
        MAX ( 'Data'[DATE_MONTH] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[DATE_MONTH]
                < DATE ( YEAR ( currDate ), MONTH ( currDate ) - 13, DAY ( currDate ) )
        )
    )
VAR EndofCalc =
    CALCULATE (
        MAX ( 'Data'[DATE_MONTH] ),
        FILTER (
            ALLSELECTED ( 'Data' ),
            'Data'[DATE_MONTH]
                < DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
        )
    )
RETURN
    CALCULATE (
        COUNT ( 'Data'[ID_NBR] ),
        FILTER (
            ALL ( 'Data' ),
            'Data'[DATE_MONTH] >= DATEVALUE ( StartofCalc )
                && 'Data'[DATE_MONTH] <= DATEVALUE ( EndofCalc )
                && 'Data'[Status] = "Active"
        )
    )

Regards,

Xiaoxin Sheng

[Muzaffar_Ali · Accepted solution]

Thanks Xiaoxin for the response, this looks good.

But I have a measure below that works and did the job 👍

Measure =
    VAR EndDate =
        EDATE ( MAX ( 'Data'[DATE_MONTH] ), -1 ) + 1 -- adjusts End Date to previous month (so pulls excluding current month)
    VAR StartDate =
        EDATE ( EndDate, -12 ) + 1 -- sets Start Date 12 months prior to End Date above
    VAR TotalActive =
        CALCULATE (
            COUNTROWS (
                FILTER ( 'Data', 'Data'[Status] = "Active" )
            ),
            -- counts rows filtered for Active only in table rather than all rows
            DATESBETWEEN (
                'Data'[DATE_MONTH],
                -- indicates column for dates between                      
                StartDate,
                -- Start Date defined above --
                EndDate -- End Date defined above --
            )
        )
    VAR TotalInactive =
        CALCULATE (
            COUNTROWS ( FILTER ( 'Lapse Data', 'Lapse Data'[Status] = "Inactive" ) ),
            -- counts rows filtered for all Inactive only in table rather than all rows
            DATESBETWEEN (
                'Data'[DATE_MONTH],
                -- indicates column for dates between                      
                StartDate,
                -- Start Date defined above --
                EndDate -- End Date defined above --
            )
        )
    RETURN
        CALCULATE ( DIVIDE ( TotalActive, TotalInactive, 0 ) )