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.
Hello. This seems simple enough but has offered me a bit of a challenge. I am seeking a DAX measure to solve this problem. I am also curious as to how a calculated column could do the same.
Eaxample data table:
Date | Cumulative Sold |
1/1/2020 | 3 |
1/2/2020 | 5 |
1/3/2020 | 10 |
1/4/2020 | 14 |
1/5/2020 | 15 |
1/6/2020 | 20 |
I need to create a table visualization that displays the two columns from the data table (easy enough) and develop a DAX measure for the third column as shown below.
Date | Cumulative Sold | Daily Num Sold |
1/1/2020 | 3 | 3 |
1/2/2020 | 5 | 2 |
1/3/2020 | 10 | 5 |
1/4/2020 | 14 | 4 |
1/5/2020 | 15 | 1 |
1/6/2020 | 20 | 5 |
Thank you for your time and effort.
Bob
Solved! Go to Solution.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
In your case:
Daily Num Sold Measure =
VAR __Current = MAX('Table'[Cumulative Sold])
VAR __Date = MAX('Table'[Date])
VAR __PreviousDate = MAXX(FILTER('Table',[Date] < __Date),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date] = __PreviousDate),[Cumulative Sold])
RETURN
__Current - __Previous
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
In your case:
Daily Num Sold Measure =
VAR __Current = MAX('Table'[Cumulative Sold])
VAR __Date = MAX('Table'[Date])
VAR __PreviousDate = MAXX(FILTER('Table',[Date] < __Date),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date] = __PreviousDate),[Cumulative Sold])
RETURN
__Current - __Previous
My apologies for the very poor colimn alignment.
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |