Forum Discussion

laernouts's avatar
laernouts
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    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 ))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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 ))