Forum Discussion

CRG's avatar
CRG
Regular Visitor
4 years ago
Solved

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 categorize...
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    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

CRG's avatar
CRG
Regular Visitor
4 years ago

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's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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