Cumulative sum comes up as empty when there is no value for that month

[aquamad96]

I have a table which has a cumulative sum (FYTD) running from the start of the financial year (Octobers which is identified using the column MAX('Air Freight Spend'[Start_of_Year])), so that every month from then It adds the previous months cost usd to the column.

However, I am having an issue with the cumulative sum becoming 0 when there is no value for that month (as seen below).

How can I fix this?

My measure

YTD Cumulative Cost =

         VAR YYTD = CALCULATE([Total Cost USD],  DATESINPERIOD('Calendar Table'[AWB Month Year], MAX('Air Freight Spend'[Start_of_Year]), DATEDIFF(MAX('Air Freight Spend'[Start_of_Year]),MAX('Calendar Table'[AWB Month Year]), MONTH), MONTH),

        ALLEXCEPT('Air Freight Spend', 'Air Freight Spend'[Dom/Int],'Air Freight Spend'[Origin_APC_Billing_region],'Air Freight Spend'[Origin_Country],'Air Freight Spend'[Origin_APC], 'Air Freight Spend'[Lane], 'Air Freight Spend'[Origin - Destination Country], 'Air Freight Spend'[Destination_Country], 'Air Freight Spend'[Destination_APC], 'Weight Band Order'[Weight Band], 'APC Map'[CtrlRegion], 'Air Freight Spend'[Suspicious Data],'Air Freight Spend'[Carrier_Mode(s)], 'Air Freight Spend'[Carrier_Service(s)], 'Air Freight Spend'[Cost_USD_Source], 'Air Freight Spend'[Consolidated AWB Flag], 'Air Freight Spend'[BillingRegion(s)], 'Air Freight Spend'[BillingCountry(s)], 'Air Freight Spend'[BillingCenter(s)], 'Air Freight Spend'[Airli], 'Air Freight Spend'[Shipment_ID(s)], 'Air Freight Spend'[CustomerName(s)], 'Air Freight Spend'[Parent(s)], 'Air Freight Spend'[Grandparent(s)], 'Air Freight Spend'[Type(s)], 'Air Freight Spend'[Account_Service(s)],  'Air Freight Spend'[Involves_SAM_Active_Flag], 'Air Freight Spend'[Involves_KAM_Active_Flag], 'Air Freight Spend'[Involves_Dangerous_Goods?], 'Air Freight Spend'[Involves_Payload?], 'Air Freight Spend'[Temperature_Control(s)], 'Air Freight Spend'[Temperature_Grouping(s)]  ,'Air Freight Spend'[PJT(s)]    ))

         RETURN IF(MAX('Calendar Table'[AWB Month Year]) >= DATE(2022, 10, 1), YYTD, BLANK())

[johnt75 · Accepted solution]

I think the problem might be that if a row doesn't exist then the max start of year may not return a value.

Try using DATESYTD instead

YTD Cumulative Cost =
VAR YYTD =
    CALCULATE (
        [Total Cost USD],
        DATESYTD ( 'Calendar Table'[AWB Month Year], "31/10" ),
        ALLEXCEPT (
            'Air Freight Spend',
            'Air Freight Spend'[Dom/Int],
            'Air Freight Spend'[Origin_APC_Billing_region],
            'Air Freight Spend'[Origin_Country],
            'Air Freight Spend'[Origin_APC],
            'Air Freight Spend'[Lane],
            'Air Freight Spend'[Origin - Destination Country],
            'Air Freight Spend'[Destination_Country],
            'Air Freight Spend'[Destination_APC],
            'Weight Band Order'[Weight Band],
            'APC Map'[CtrlRegion],
            'Air Freight Spend'[Suspicious Data],
            'Air Freight Spend'[Carrier_Mode(s)],
            'Air Freight Spend'[Carrier_Service(s)],
            'Air Freight Spend'[Cost_USD_Source],
            'Air Freight Spend'[Consolidated AWB Flag],
            'Air Freight Spend'[BillingRegion(s)],
            'Air Freight Spend'[BillingCountry(s)],
            'Air Freight Spend'[BillingCenter(s)],
            'Air Freight Spend'[Airli],
            'Air Freight Spend'[Shipment_ID(s)],
            'Air Freight Spend'[CustomerName(s)],
            'Air Freight Spend'[Parent(s)],
            'Air Freight Spend'[Grandparent(s)],
            'Air Freight Spend'[Type(s)],
            'Air Freight Spend'[Account_Service(s)],
            'Air Freight Spend'[Involves_SAM_Active_Flag],
            'Air Freight Spend'[Involves_KAM_Active_Flag],
            'Air Freight Spend'[Involves_Dangerous_Goods?],
            'Air Freight Spend'[Involves_Payload?],
            'Air Freight Spend'[Temperature_Control(s)],
            'Air Freight Spend'[Temperature_Grouping(s)],
            'Air Freight Spend'[PJT(s)]
        )
    )
RETURN
    IF (
        MAX ( 'Calendar Table'[AWB Month Year] ) >= DATE ( 2022, 10, 1 ),
        YYTD,
        BLANK ()
    )

[v-saisrao-msft]

Hi @aquamad96,

Thank you for reaching out to Microsoft Fabric Community.

The measure provided is accumulating the costs month by month.

If a month is missing, we need to check the previous months and carry forward the last known total instead of starting from zero again.

We can use the (COALESCE)COALESCE function, which will return the last value if the current value is missing.

Please provide us with sample data if you still encounter the same issue.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

[johnt75 · Accepted solution]

I think the problem might be that if a row doesn't exist then the max start of year may not return a value.

Try using DATESYTD instead

YTD Cumulative Cost =
VAR YYTD =
    CALCULATE (
        [Total Cost USD],
        DATESYTD ( 'Calendar Table'[AWB Month Year], "31/10" ),
        ALLEXCEPT (
            'Air Freight Spend',
            'Air Freight Spend'[Dom/Int],
            'Air Freight Spend'[Origin_APC_Billing_region],
            'Air Freight Spend'[Origin_Country],
            'Air Freight Spend'[Origin_APC],
            'Air Freight Spend'[Lane],
            'Air Freight Spend'[Origin - Destination Country],
            'Air Freight Spend'[Destination_Country],
            'Air Freight Spend'[Destination_APC],
            'Weight Band Order'[Weight Band],
            'APC Map'[CtrlRegion],
            'Air Freight Spend'[Suspicious Data],
            'Air Freight Spend'[Carrier_Mode(s)],
            'Air Freight Spend'[Carrier_Service(s)],
            'Air Freight Spend'[Cost_USD_Source],
            'Air Freight Spend'[Consolidated AWB Flag],
            'Air Freight Spend'[BillingRegion(s)],
            'Air Freight Spend'[BillingCountry(s)],
            'Air Freight Spend'[BillingCenter(s)],
            'Air Freight Spend'[Airli],
            'Air Freight Spend'[Shipment_ID(s)],
            'Air Freight Spend'[CustomerName(s)],
            'Air Freight Spend'[Parent(s)],
            'Air Freight Spend'[Grandparent(s)],
            'Air Freight Spend'[Type(s)],
            'Air Freight Spend'[Account_Service(s)],
            'Air Freight Spend'[Involves_SAM_Active_Flag],
            'Air Freight Spend'[Involves_KAM_Active_Flag],
            'Air Freight Spend'[Involves_Dangerous_Goods?],
            'Air Freight Spend'[Involves_Payload?],
            'Air Freight Spend'[Temperature_Control(s)],
            'Air Freight Spend'[Temperature_Grouping(s)],
            'Air Freight Spend'[PJT(s)]
        )
    )
RETURN
    IF (
        MAX ( 'Calendar Table'[AWB Month Year] ) >= DATE ( 2022, 10, 1 ),
        YYTD,
        BLANK ()
    )

[aquamad96]

Fantastic thank you - this solution works!!

Could I also ask how a DATESYTD can be calculated for the previous year? ie

    YYTDPY = CALCULATE([Total Cost USD],  

   DATESYTD ( 'Calendar Table'[AWB Month Year], "31/09" )  ,    --- ( a -1 here???)

   ALLEXCEPT('Air Freight Spend', 'Air Freight Spend'[Dom/Int],'Air Freight Spend'[Origin_APC_Billing_region],'Air Freight Spend'[Origin_Country],'Air Freight Spend'[Origin_APC], 'Air Freight Spend'[Lane], 'Air Freight Spend'[Origin - Destination Country], 'Air Freight Spend'[Destination_Country], 'Air Freight Spend'[Destination_APC], 'Weight Band Order'[Weight Band], 'APC Map'[CtrlRegion], 'Air Freight Spend'[Suspicious Data],'Air Freight Spend'[Carrier_Mode(s)], 'Air Freight Spend'[Carrier_Service(s)], 'Air Freight Spend'[Cost_USD_Source], 'Air Freight Spend'[Consolidated AWB Flag], 'Air Freight Spend'[BillingRegion(s)], 'Air Freight Spend'[BillingCountry(s)], 'Air Freight Spend'[BillingCenter(s)], 'Air Freight Spend'[Airlin], 'Air Freight Spend'[Shipment_ID(s)], 'Air Freight Spend'[CustomerName(s)], 'Air Freight Spend'[Parent(s)], 'Air Freight Spend'[Grandparent(s)], 'Air Freight Spend'[Type(s)], 'Air Freight Spend'[Account_Service(s)],  'Air Freight Spend'[Involves_SAM_Active_Flag], 'Air Freight Spend'[Involves_KAM_Active_Flag], 'Air Freight Spend'[Involves_Dangerous_Goods?], 'Air Freight Spend'[Involves_Payload?], 'Air Freight Spend'[Temperature_Control(s)], 'Air Freight Spend'[Temperature_Grouping(s)]  ,'Air Freight Spend'[PJT(s)]    ))

[johnt75]

The second parameter to DATESYTD doesn't need a year part, and in fact will ignore the year if it is present. The function assumes that the end of year will be on the same day every year, so you only need to specify the month and day.

[aquamad96]

Sorry, what I meant was I want to calculate the variance to last year for the cumulative. So I need to work out the previous years value for that month. Ie March 2024 variance against March 2025.

In context of the measure, how would the VAR YYTDPY change to make it last years value?

[johnt75 · Accepted solution]

Rather than specifying the code again, I would have a base measure as e.g. [FYTD] then your variance would be

FYTD vs Previous =
VAR CurrentYear = [FYTD]
VAR PrevYear =
    CALCULATE ( [FYTD], DATEADD ( 'Date'[Date], -1, YEAR ) )
VAR Result =
    DIVIDE ( CurrentYear - PrevYear, PrevYear )
RETURN
    Result

[aquamad96]

Thats perfect thank you 🙂