Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a slicer where I can choose dates to calculate the running total for any day that I select and display it on a line graph.
Requirement:
I need to overlay the graph with the running total of the previous day. How do I rewrite my measure such that when I select the same date above, the graph will display the running total for both 20220114 and 20220113?
Data and Measure used:
I have a Date Dimension Table that looks like the one below:
A day which is identified by the DateID column starts from 8pm the previous day and ends 7:59pm the following day.
So to calculate the Running Total I use the below measure :
CumulativeNumberOfOrders =
CALCULATE( COUNTROWS('Fact OrderTable'),
FILTER(ALLSELECTED('Dim DateTimeDim'),'Dim DateTimeDim'[DateTimeForPowerBI]<=MAX('Dim DateTimeDim'[DateTimeForPowerBI])
)
)
@Anonymous , If you select one date and want data for more than one date you need independent date table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max && 'Date'[Date] <= max('Date'[Date]) ))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hi @amitchandak
the solution you provided does not seem to give me the graph I needed:
HI @amitchandak the solution you provided does not seem to give me cumulative graph.
I need to display the cumulative sum as displayed on the LHS, however the script you provided displays individual orders at specific times.
hi @amitchandak , did you mean have two dates table ? Date1 and Date, where Date1 is not connected to anything and Date is connected to my fact table?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |