SUM date to date+3

[Pikachu-Power]

hello,

i am looking for a formula that sum up the numbers from date until date + 3 days. For example for 01.01.2020 the numbers from date 01.01.2020 to 04.01.2020. Would be 8.  The 15 is wrong... that should be 9. 

Some ideas?

[amitchandak · Accepted solution]

@Pikachu-Power  Try with a date calendar

Rolling 3 day = CALCULATE(sum(Table[Number]),DATESINPERIOD('Date'[Date],MAX(Table[Date]),3,Day))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[parry2k]

@Pikachu-Power As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

3 Days Total = 
VAR __selectedDAte = SELECTEDDATE ( DateTable[Date] )
RETURN
CALCULATE ( SUM ( Table[Number] ), DATESBETWEEN ( DateTable[Date], __selectedDate, __selectedDate + 3 ) )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!