Cumulative totals - Current & last FY

[KoalaPBI]

Hi there,

I want to calculate cumulative signups for current FY & last FY. FY starts on 1st July & ends on 30th June. 

I created a measure to calculate cumulative total for current FY, but it is incorrect. should be 15 in Aug instead of 14. Please let me know what is wrong with this measure and also provide a measure to calculate cumulative signups for last FY (FY22)

Here is the pbi file : https://1drv.ms/u/s!Ag919_pO_UKrghrxssJ1dthLEMvu?e=q2183a

many thanks!

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I think ID = 111 is in both Jul and Aug (Duplicated).

So, I think if the measure distinctcount IDs, it is the correct number.

However, If you still want to show 15, even ID=11 is shown in the both months, please try the below measure.

Signups Current FY_Cum =
VAR _newtable =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER (
                ALL ( Datedim ),
                Datedim[Date] <= MAX ( Datedim[Date] )
                    && Datedim[Is Current FY] = 1
            ),
            Datedim[FYYear],
            Datedim[FY_Monthname],
            Datedim[FYMonthNum]
        ),
        "@singup", CALCULATE ( DISTINCTCOUNT ( 'Table'[Id] ) )
    )
RETURN
    SUMX ( _newtable, [@singup] )

[KoalaPBI]

thanks @Jihwan_Kim , Would you please also let me know how can I calculate cumulative for last FY (FY22). I can't use the filter Datedim[Is Current FY] <>1 as in my dataset there is data from FY21 as well ?

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

Could you please check the attached pbix file, if it shows the correct numbers?

Thanks.