Forum Discussion
Solved
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...
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 () )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
Super UserI 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 ()
)
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.
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
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