Hello,
I need support on calculating a cumulative sum of a measure that is using dateadd. Currently the cumulative measure does not start at the date that I want it to...
Let me describe my case:
- I have a facts table containing values per day. Let's say the first value is from 1 August 2022
- I create a measure that calculates a sum of the values.
- I create another measure that shifts the values of this measure by certain days by using dateadd. Let's say, all values are shifted backwards for 7 days. This means, that the dateadd measure already starts on 25 July 2022 (= 7 days backwars from 1 August)
- I then want to calculate a cumulative sum of the shifted values, HOWEVER that measure should only start calculation at the first day when there are also values for the initial measure.
- In my case, the first value is on 1 August
- The goal is to start cumulating the shifted values from that day onwards
- The shifted value on 1 August is 487. This is the value that has been shifted from 8 August backwards for 7 days
- Currently, the cumulative value of the shifted values starts at 25 July
The following picture contains my sample data, the measures and also the expected value:
Appreciated your comments on finding a solution 😉
Thanks!
Here are all measure that I have created
Value Sum = SUM(Data[Value])Time shift in full days = CALCULATE(
Roundup(AVERAGE(Data[Timeshift]),0),
REMOVEFILTERS('Calendar')
)Value shifted by Days =
CALCULATE(
[Value Sum],
DATEADD(
'Calendar'[Date],
[Time shift in full days],
DAY
)
)Value shifted cumulated = CALCULATE(
[Value shifted by Days],
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date])
)
)
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
