Forum Discussion
Year to date three days.
Hi,
This table is filtered by the date table. If i select a date from the date table i want the folowing result:
The sum of the sales from a selected date and looking back 3 days.
If i select 2021-12-09 i want to get 10 as sales result
If i select 2021-12-08 i want to get 18 as sales result
If i select 2021-12-07 i want to get 14 as sales result
Sales Date | Sales |
| 2021-12-09 | 2 |
| 2021-12-08 | 5 |
| 2021-12-07 | 3 |
| 2021-12-06 | 10 |
| 2021-12-05 | 1 |
Can anyone tell me the correct formule to calculate this results?
laernouts , with help from a date tbale try a measure like
Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[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 :radacad sqlbi My Video Series Appreciate your Kudos.
or
Rolling 3
=
CALCULATE(SUM(Sales[Sales]),filter(allselected(Table),Table[date]<=max(Table[Date]) &&date[date]>=max(Table[Date]) -3 ))
1 Reply
- amitchandak
Super User
laernouts , with help from a date tbale try a measure like
Rolling 3 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[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 :radacad sqlbi My Video Series Appreciate your Kudos.
or
Rolling 3
=
CALCULATE(SUM(Sales[Sales]),filter(allselected(Table),Table[date]<=max(Table[Date]) &&date[date]>=max(Table[Date]) -3 ))