topic Re: Running Total Question in DAX Commands and Tips

Running Total Question

CRG — Wed, 04 May 2022 18:49:15 GMT

Hello all,

I'm trying to create a "Z Chart" for a 12 month period, with a running total of expenditures [Amount] which are all categorized into periods [Per No] which are simply months categorized from 1-12. The issue I have, and I have read through the various posts on this, I can't limit the running total to only display the months where there is data. So for example, I only want to show the running total months for periods 1,2,3 when no data exists for 4+. Instead I get a cumulative total that flatlines and is unsightly.

I have a simple running total quick measure as follows:

Amount running total in Per No =

CALCULATE(

SUM('Combined Data'[ Amount]),

FILTER(

ALLSELECTED('5-Year Trend'[Per No ]),

ISONORAFTER('5-Year Trend'[Per No ], MAX('5-Year Trend'[Per No ]), DESC)

)

Any guidance would be greatly appreciated.

Re: Running Total Question

Whitewater100 — Sun, 01 May 2022 18:37:53 GMT

Hi:

You can start your measure

Amount RT Period =

var amount =SUM('Combined Data'[ Amount])

return
IF( amount > 0,

CALCULATE(

SUM('Combined Data'[ Amount]),

FILTER(

ALLSELECTED('5-Year Trend'[Per No ]),

ISONORAFTER('5-Year Trend'[Per No ], MAX('5-Year Trend'[Per No ]), DESC)

)

Or if you have a measure for amount

Amount = SUM('Combined Data'[ Amount])

You can start the measure as

= IF[Amount] >0,

CALCULATE(

SUM('Combined Data'[ Amount]),

FILTER(

ALLSELECTED('5-Year Trend'[Per No ]),

ISONORAFTER('5-Year Trend'[Per No ], MAX('5-Year Trend'[Per No ]), DESC)

)

I hope this helps.

Re: Running Total Question

CRG — Mon, 02 May 2022 12:45:49 GMT

Thank you for your thoughts.

I hope I was clear in my original post, it currently looks like this:

The green line corresponds to expenditures in period 1-4, while the blue line is the Running Total measure. I'm trying to not have any values in Per 5+, instead of the static $7M. This is somewhat more complicated as I have a historic trend line (not visible in the picture above) that I also map to the chart over the entire Period from 1-12; however, if I could just get it to not show values when there are no expenditures in the period, it would be dynamite.

When I enter your suggestion, I get:

Very much appreciate your time and I fully realize I am not explaining it well.

Re: Running Total Question

Whitewater100 — Mon, 02 May 2022 14:17:18 GMT

Hi: I will first place your measure and then explain how to set up your model better for future analysis.

Cumulative RT Sales =

var runtotal = CALCULATE(

SUM('Combined Data'[ Amount]),

FILTER(

ALLSELECTED('5-Year Trend'[Per No ]),

ISONORAFTER('5-Year Trend'[Per No ], MAX('5-Year Trend'[Per No ]), DESC)

)

Return

IF(CALCULATE(SUM('Combined Data'[ Amount])) = BLANK(),BLANK(), runtotal)

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Running Total Sales (e.g.) =

var runningtotal = CALCULATE(SUM(Sales[Sales Amt]), FILTER(ALLSELECTED(Dates),

Dates[Date] <= MAX(Dates[Date])))

Return

IF(CALCULATE(SUM(Sales[Sales Amt])) = BLANK(),BLANK(), runningtotal)

Yes can you please add Date Table (new table and mark as date table). Please connect Date Table Month field to your Fact Table Date field. I will post Date Table Code below and an example file is here.

https://drive.google.com/file/d/1uNb3cBh4l89_EtPAZpjZayYAH2WbtNfJ/view?usp=sharing

Connecting Date Table:

For measure:

Running Total Sales (e.g.) =

var runningtotal = CALCULATE(SUM(Sales[Sales Amt]), FILTER(ALLSELECTED(Dates),

Dates[Date] <= MAX(Dates[Date])))

Return

IF(CALCULATE(SUM(Sales[Sales Amt])) = BLANK(),BLANK(), runningtotal)

Dates =

-- Specify a start date and end date

VAR StartDate = Date(2021,1,1)

VAR EndDate = Today() + 243

VAR FiscalMonthEnd = 12

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

Years,

"Month",MONTH([Date]),

“Month Name” ,FORMAT([Date], “MMM”),

"Year and Month Number",FORMAT([Date],"YYYY-MM"),

"Year and Month Name",FORMAT([Date],"YYYY-MMM"),

"Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

"Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

-- Add the Quarter and other quarter related data for each date

VAR Quarters = ADDCOLUMNS(

Months,

"Quarter",ROUNDUP(MONTH([Date])/3,0),

"Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

-- Add the Day and other day related data for each date

VAR Days = ADDCOLUMNS(

Quarters,

"Day",DAY([Date]),

"Day Name",FORMAT([Date],"DDDD"),

"Day Of Week",WEEKDAY([Date]),

"Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date

VAR Weeks = ADDCOLUMNS(

Days,

"Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

"Week of Year (Sunday)",WEEKNUM([Date],1),

"Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

Weeks,

"Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

RETURN WorkingDays