Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
CRG
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

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:

Whitewater100_1-1651500716568.png

 

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)

 

Whitewater100_0-1651500423844.png

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

View solution in original post

3 REPLIES 3
CRG
Regular Visitor

Thank you for your thoughts. 

 

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

Demo picture.jpg

 

 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:

 

Demo picture 2.jpg

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

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:

Whitewater100_1-1651500716568.png

 

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)

 

Whitewater100_0-1651500423844.png

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
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.