Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
My data needs to be reported at a period level and a period is roughly 28 days, but not always. I need to report the cumulative sales at the day level for each period. The dax functions DATESMTD and DATESYTD are able to provide a cumulative sales at the month and year level. Are there any ways I could do this at the period level? Am I able to see the underlying code for those functions and make my own?
I can use calculate([Total Sales], DATESMTD(Dates[Date])) to get the cumulative sales for the month, but I need to get it at the period level.
My dates table is designed below.
Date | Period | Fiscal Year
First Create date period table like following:
| PeriodID | StartDate | EndDate |
|----------|-------------|-------------|
| 1 | 2024-01-01 | 2024-01-28 |
| 2 | 2024-01-29 | 2024-02-25 |
| 3 | 2024-02-26 | 2024-03-24 |
| ... | ... | ... |
//And then use this measure
CumulativeSalesPeriod =
CALCULATE(
SUM('YourTable'[Total Sales]),
FILTER(
ALL('YourTable'[Date]),
'YourTable'[Date] <= MAX('YourTable'[Date]) &&
'YourTable'[Date] >= MIN('YourTable'[Date]) &&
'YourTable'[Date] >= RELATED('PeriodTable'[StartDate]) &&
'YourTable'[Date] <= RELATED('PeriodTable'[EndDate])
)
)
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
@mds123 You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
User | Count |
---|---|
93 | |
85 | |
77 | |
68 | |
62 |
User | Count |
---|---|
112 | |
97 | |
95 | |
64 | |
58 |