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'm trying to write some fixed time period Running Total measures (e.g. 90-Day Revenue) and have had some issues stemming from having a date table that runs out a couple of years in front of the sales table.
I'm using this pattern to limit the dates to when I have sales:
90-Day Sales =
CALCULATE(
[Sales],
DATESINPERIOD(Date_Table[Date], MAXX(Sales_Table, Sales_Table[Date],
-90,
DAY)
When the filter context is my whole company, this measure works fine. Here is a typical visualization of one of these measures (running 90-Day Distribution by Week Ending):
However, when the filter context includes specific items or territories, the MAXX(Sales_Table, Sales_Table[Date]) will refer to different dates for each row, throwing off totals.
I want to refer to dates no later than the last day of sales, and then I want to see the last 90 days of sales for each row from that date (not from the individual row's last sales date).
Thanks for your help,
Ben
@Anonymous , Try a measure like
CALCULATE([Sales],DATESINPERIOD(Date_Table[Date], MAXX(Sales_Table, Sales_Table[Date]),-90,DAY))
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |