DAX Stop Running Total with COUNTA at last date with values

[Anonymous]

Hi, 

I have a running total measure as per below, and I'm struggling with the same issue as many else - how to stop the count at last date with values to avoid the horizontal line in my graph beyond that date. I tried updating the DAX to use a date range, but then all the rows that doesn't have a value is left out of the calculation and I'm unable to use the forecast function because my "data is too irregular". 

The data I'm counting are number of date entries, i.e. I cannot sum them directly. 

Any suggestions on how to solve this would be much appreciated! 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Try:

Measure = 
var _max = CALCULATE(MAX(CALENDAR1[Date]),ALL(CALENDAR1))
var _today = TODAY()
return
CALCULATE(COUNTA('table'[Actual Date]),FILTER(ALL(CALENDAR1),CALENDAR1[Date]<=_max&&_max<=_today))

If it doesn't work, please show some sample data to us so that we could test the DAX formula.

Best Regards,

Jay

[amitchandak]

@Anonymous , Try measure like these examples

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date]) && date[date] <=Today() ))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date] <=max(date[Date]) && date[date] <=Today() ))

[Anonymous]

Hi @amitchandak 

Thanks, though your solutions gives me the same problem as my modified DAX, it excludes all the dates that doesn't have a value and thereby making the forecast function unavailable (which I must have). 

Do you have any more specific suggestions to my problem? 

[amitchandak]

@Anonymous , It is better to have an independent date table, when want to restrict the dates by slicer and show more than that

So date1 is disconnected used in slicer and date is joined

sales =
var _max = maxx(allselected('Date1'),'Date1'[Date])
return
CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date]) && date[date] <=_max)) +
CALCULATE(SUM(forecast[forecast Amount]),filter(all(date),date[date] <=max(date[Date]) && date[date] >_max))

Date from date Table is used on axis

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI