Forum Discussion
Impactful IndividualSUM date to date+3
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?
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
2 Replies
- parry2k
Super User
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-calculations/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!
- amitchandak
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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
