Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running Total Measure Works By Day but Not by Week

Hello,   I am having a problem with displaying the running total of a column in a Line and Stacked Column Chart visual. This visual is to show forecasted and actual shipping numbers. The X axis h...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Edited:

     

    [The Right Measure] =
var MaxFiscalDate = MAX( 'Fiscal Calendar'[Calendar Date] )
var Result =
	CALCULATE(
	    [Shipment Forecast],
	    KEEPFILTERS( 'Fiscal Calendar'[Calendar Date] <= MaxFiscalDate ),
	    ALLSELECTED( 'Fiscal Calendar' )
	)
return
	Result

     

     

    There would be too much to explain about how ALLSELECTED works...

Anonymous's avatar
Anonymous
Not applicable
5 years ago

This works for the green line on my visual!
Unfortunatley both the green line and the blue columns (Total Shipped) were having the same issue, and following the same logic with Total Shipped didn't work. In fact, it made it not cumulative when drilling down on both Calendar Date and Fiscal Week.

Here is the original measure:

 

Total Shipped running total in Fiscal Week =
CALCULATE(
    [Total Shipped],
    FILTER(
        ALLSELECTED('Fiscal Calendar'[Calendar Date]),
        ISONORAFTER('Fiscal Calendar'[Calendar Date], MAX('Fiscal Calendar'[Calendar Date]), DESC)
    )
)


And here is the one I tried to model off of your solution:

 

Total Shipped running total in Fiscal Week =
var MaxFiscalDate = MAX( 'Fiscal Calendar'[Calendar Date] )
var Result =
    CALCULATE(
     [Total Shipped],
     KEEPFILTERS( 'Fiscal Calendar'[Calendar Date] <= MaxFiscalDate ),
     ALLSELECTED( 'Fiscal Calendar' )
    )
return
    Result
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Anonymous 

 

I created a simple model to test it and it works the way it should. If it does not work in your case, then it means... there's something amiss about your model.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I think you must be right. Thank you for the help!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Anonymous After fixing my model, the issue was resolved. However I noticed that that when I select multiple options in any slicer, the running total resets after that time interval. With one selected or none in each slicer, there are no issues. In the example below, I have weeks 1-5 selected.

       

      The Total Shipped (Blue) and Shipment Forecast (Green) follow the format of your measure above. Any idea on why this is happening?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Instead of going back and forth aimlessly and fruitlessly... please place a link to the file in here. I'll then resolve all the issues you have. Promise.