Forum Discussion
aquamad96
1 year agoFrequent Visitor
Cumulative sum comes up as empty when there is no value for that month
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 mo...
- 1 year ago
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 () ) - 1 year ago
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
1 year agoFrequent Visitor
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
Super User
1 year agoRather 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
- aquamad961 year agoFrequent Visitor
Thats perfect thank you 🙂