Running total--exclude one column value in stacked column but display full year trend

[MarkD1733]

I have a running total of spend and monthly spend showing in a combination stacked column and line chart.  The stacked bars are monthly spend, but a monthly budget value is mixed in as well.   I don't want to display that budget value in the column so I slice it out.  But when I don't have spend values in a month, the cumulative budget total doesn't display...it omits the months with no spend (i.e., Nov and Dec below).  So, I am using a calendar table and am "showing items with no data."  But as you can see, my budget trend ends at October because I have no spend in November and December and have excluded the budget from the monthly totals.  Is it possible to make that trend line continue through Nov and Dec?

 
Here is my DAX code for the budget cumulative trend.  It contains some filtering (for dynamic filtering):

[v-yadongf-msft · Accepted solution]

Hi @MarkD1733 ,

This is my test table ( no spend in November and December)

Create a calendar table:

CalendarTable = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2022,12,31)),"Month",FORMAT([Date],"mmm"),"MonthNumber",MONTH([Date]))

Create following measures:

Cumulative_budget = CALCULATE(SUM('Table'[Amount USD]),'Table'[Category] = "Month Budget",FILTER(ALL('CalendarTable'),'CalendarTable'[Date] <= MAX('CalendarTable'[Date])))

Cumulative_spend = CALCULATE(SUM('Table'[Amount USD]),'Table'[Category] = "Month Spend",FILTER(ALL('CalendarTable'),'CalendarTable'[Date] <= MAX('CalendarTable'[Date])))

Sum_amount USD = CALCULATE(SUM('Table'[Amount USD]),FILTER(ALL('CalendarTable'),'CalendarTable'[Month] = SELECTEDVALUE('CalendarTable'[Month])))

I think this is the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.