Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Running Total Removing Blanks

Hi Everyone, could use some help. I have a DAX caluclation issue in Power BI. I have a date and sales table joined via a one-to-many relationship on the 'date' column.   I want to calulate a run...
Need Help
Tips and Tricks
Tutorial Requests
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

For context, what I am tring to do is create the table to stop dsplaying the running total after the last sales date and have the line graph to stop displaying after the last sales date as it plateaus and becomes flat. 

 

The requirement is that there must be a line graph that is categorical, with a running total that stops after the last sales date. And a slicer that can select different months where the running total should reset every month. I completed this using my measure (Running Total Measure) but it shows blanks and gaps in previous months where there is no sales on a date.

 

Using your measure worked but then it it keeps running until the end of the month and becomes flat. 

 

Ideally I need a combination of the two, a running total that has no blanks for any date and it should stop running after the last sales date. 

 

Any help would be appreciated.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

You may download my PBI file from here.

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

     

    It looks like it's worked. For my own benefit, could you explain why you did this calculation and the logic that is occuring in it.

    Date till which actual sales are available = 
CALCULATE(MAX(Sales[SalesDate]),FILTER(ALL('Date'[Date]),[Sales1]>0))

Sales1 = SUM(Sales[Sales])

And why does this not work in this scenario, why do you have to filter it by date and sales? 
MAX(Sales[SalesDate])

     

    The following calculation you provided for running total:

    Running Total Measure = 
IF(MAX('Date'[Date])>[Date till which actual sales are available], blank(),
calculate([Sales1],datesmtd('Date'[Date])))

    For this calculation, just wondering why you used MAX('Date'[Date]), wouldn't this just apply for the maximum date in the date table (i.e. 30 March 2022) being blank and not every date after the sales date.

     

    Or am I misinterpreting the logic. Any explanation on all of this would help me going forward.

     

    Thanks.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      The first calculation is to determine the last date till which there is data in the actual sales table.  Via this measure, I have applied a filter on where sales > 0 and then calculated the max date from the actual sales table.  Max(Date[Date]) will return the date of the existing row in the matrix visual.  So if the date in the matrix visual is > [Date till which actual sales are available] then show a blank, else perform the MTD calculation.

      If my previous reply helped, please mark it as Answer.