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,
I have a table with dates and sales. I would like to present on a chart or on a table, the sales by month, however if the month equals the latest month available in the data, then it should display the data from the previous month.
Formula:
Measure = CALCULATE(SUM(Table1[Sales]),FILTER(all(Table1),(Table1[Date])=EOMONTH(MAX(Table1[Date]),IF(CALCULATE(MAX(Table1[Date]))=CALCULATE(MAX(Table1[Date]),ALL(Table1)),-2,-1))+1))
Displaying the measure on a table, this is what it looks like: as you can see the problem is in the first row. it should display 2:
Date Sales Measure
7/1/2017 1 (empty)
6/1/2017 2 2
5/1/2017 3 3
4/1/2017 4 4
3/1/2017 5 5
2/1/2017 6 6
1/1/2017 7 7
Does anyone have an idea how to solve this?
thanks
LS
Solved! Go to Solution.
Solution:
Measure = CALCULATE(SUM(Table1[Sales]),FILTER(ALL(Table1[Date]),CALCULATE(max(Table1[Date]))=EOMONTH((MAX(Table1[Date])),IF((MAX(Table1[Date]))=CALCULATE(MAX(Table1[Date]),ALL(Table1)),-2,-1))+1))
Solution:
Measure = CALCULATE(SUM(Table1[Sales]),FILTER(ALL(Table1[Date]),CALCULATE(max(Table1[Date]))=EOMONTH((MAX(Table1[Date])),IF((MAX(Table1[Date]))=CALCULATE(MAX(Table1[Date]),ALL(Table1)),-2,-1))+1))
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |