Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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
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
@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() ))
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?
@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
User | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |