Running Total Question

[CRG]

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.

[CRG]

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.

[Whitewater100 · Accepted solution]

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

[Whitewater100]

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.