Cumulative of measure using DATESYTD returning zero

[jhd]

Hi, I am trying to calculate the cumulative of a measure I have created. I have had success in the past doing this using:

CALCULATE(
[measure],
DATESYTD('Calendar'[Date],"3/31"),
VALUES('Calendar'[FMName])
)

In other words, a running total for the fiscal year (FMName), which ends on March 31st.

However, it is not working for for one of my measures, and returns zero for all time periods.

VAR FIRSTMONTH =
CALCULATE (
MIN (MASTER_DATA[DATE_REC(FYMonth Index)]),
ALL ( MASTER_DATA[SUPPLIER_NO], MASTER_DATA[ACT], MASTER_DATA[PLANT] ,MASTER_DATA[CURR],MASTER_DATA[UOM],MASTER_DATA[DATE_REC]),
ALL(Plants),
ALLEXCEPT('Calendar','Calendar'[FYName]),
MASTER_DATA[M_UNITS_REC] > 0
) 

RETURN
SUM(MASTER_DATA[M_UNITS_REC])*
DIVIDE(
    CALCULATE(
        [GBP_VALUE],
        VALUES('Calendar'[FYName]),
        ALL(Plants),
        ALL(MASTER_DATA[CURR], MASTER_DATA[ACT], MASTER_DATA[PLANT], MASTER_DATA[SUPPLIER_NO], MASTER_DATA[UOM]),
        ALL('Calendar'),
        'Calendar'[Fiscal_Month_Number]=FIRSTMONTH
    ),
    CALCULATE(
        SUM(MASTER_DATA[M_UNITS_REC]),
        VALUES('Calendar'[FYName]),
        ALL(Plants),
        ALL(MASTER_DATA[CURR], MASTER_DATA[ACT], MASTER_DATA[PLANT], MASTER_DATA[SUPPLIER_NO], MASTER_DATA[UOM]),
        ALL('Calendar'),
        'Calendar'[Fiscal_Month_Number]=FIRSTMONTH
    )
)

By testing various parts of the measure, I believe the issue is with the variable FIRSTMONTH.

Any ideas why this is happening?

Thanks, James

[jhd · Accepted solution]

I found this reply from @OwenAuger on another thread:

https://community.powerbi.com/t5/Desktop/Replacing-lt-year-end-date-gt-in-TOTALYTD-Function-with-TOD...

In short, the problem is that DATESYTD cannot handle dynamic variables.

I have therefore fixed the problem by using filters instead of DATESYTD

CALCULATE(
[£APV(GID)],
FILTER(
    ALL('Calendar'),
    AND(
        'Calendar'[Fiscal_Month_Number]<=MAX('Calendar'[Fiscal_Month_Number]),
        'Calendar'[Fiscal_Year]=MAX('Calendar'[Fiscal_Year])
    )
)
)