cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Running Total Question

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.
1 ACCEPTED SOLUTION
Solution Sage

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.

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

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

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

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

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

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.

Weeks,

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

RETURN WorkingDays

3 REPLIES 3
Regular Visitor

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.

Solution Sage

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.

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

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

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

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

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

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.

Weeks,

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

RETURN WorkingDays

Solution Sage

Hi:

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.