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

 

Need Help
Tips and Tricks
Tutorial Requests

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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