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! Hope you can help with this, I have a measure to add up sales of last 3 weeks, the measure is :
_P3 = CALCULATE(SUM(Dashboard_Weekly[Value]),DATESBETWEEN(Dashboard_Weekly[Week],[_L3W],[_ThisWK]))
Where _ThisWK = SELECTEDVALUE(Dashboard_Weekly[Week]) and _L3W = [_ThisWK]-14
So far the result I am getting is shown in the table below, as you'll see the measure works fine when there are sales for current week (22/07/2018, 15/07/2018, etc..) however it is not working properly if the current week has no sales, for example for week 29/07/2018 the result should be = 12 (8 from 22/07 plus 4 from 15/07).
I also tried a different formula :
L3 = CALCULATE(SUM(Dashboard_Weekly[Value]),DATESINPERIOD(Dashboard_Weekly[Week],LASTDATE(Dashboard_Weekly[Week]),-21,DAY),ALLSELECTED(Dashboard_Weekly[Week]))
but with that one I just get blanks where there are no sales on current week.
Appreciate any help you can provide, I can share my PBIX if necessary.
Thanks in advance!
Ale
Hi @aavilap
Try this formula
Measure = CALCULATE(SUM(Sheet7[current]),DATESINPERIOD(Sheet7[date],MAX(Sheet7[date]),-21,DAY),ALL(Sheet7))
Best Regards
Maggie
Thanks @v-juanli-msft! I have managed to solve it by creating an independent dates table and creating the relation between my Data table and this new dates 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 |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |