Running Total Removing Blanks

[Anonymous]

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 running total that resets every month up until the latest sales date.

I have a current calculation that works but it shows blank values where there is no sales[salesdate] that equals a date[date] (generally weekends). The current calculation does end on the latest sales date, however I just want these blank values to display the last value if possible.

The calculations and table I am using:

Simple Sales Measure:

Sales = SUM(Sales[Sales])

Current Running Total Measure:

VAR _LastSalesDate = MAX(Sales[SalesDate])

Return

IF(

SELECTEDVALUE('Date'[Date]) <= _LastSalesDate,

CALCULATE([Sales],

DATESMTD(('Date'[Date]))))

Any help would be appreacited, this problem has recently become the bane of my existence. 

[Ashish_Mathur]

Hi,

To your visual, ensure that you drag the Date column from the Date table.  Write this measure:

Current Running Total Measure =calculate([Sales],datesmtd(calendar[date]))

Hope this helps.

[Anonymous]

Hi,

Thanks alot for your help. This fills in the blanks that I was getting before and computes a new total starting every month. 

I've added the date column from the date table and the calculation you posted but how do I get it to stop showing any values or show blanks past the sales date. The last sales date is the 04/05. 

Thanks

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

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 · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

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]

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.