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 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.
  • 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

3 Replies

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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.
  • 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