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
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
aquamad96
1 year agoFrequent Visitor
Thats perfect thank you 🙂