Forum Discussion

vanessa's avatar
vanessa
Post Patron
9 years ago

Dynamic Last 7 day

  I am trying to calculate the sales for the last 7 days, but the last 7 days will be dynamic, based on the date selected in a slicer. eg. if the date selected in the slicer is 2017/07/18, then the...
Need Help
Show and Tell
Rameshwar's avatar
Rameshwar
Frequent Visitor
9 years ago

Hi Anonymous

 

Your formula works correctly in PowerBI Desktop when I have In Memory data. 

 

However, its not working when I've PowerBI Desktop conected to SSAS Tabular instance (1103 compatibility - SQL2014) with Direct Query connection. It gives me syntax error for VAR function.

 

Is VAR function not supported in SSAS, specifically to 1103 compatibility level?

 

Thanks for your help!

 

Thanks,

Rameshwar 

 

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi,

 

If you are working with a date table, you could also create a calculated column within the date table that's is called 'IsLast7Days'. The column value equals true for every day that's within last 7 days. Based on that, you can create a calculation including a filter (Where IsLast7Days equals true).

  • Rameshwar's avatar
    Rameshwar
    Frequent Visitor
    9 years ago

    Anonymous

     

    The Calculated column will not work here. This Measure needs to be dynamic. The user might select any date from Slicer and depending upon that we should go back previous 7 days. 

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    Hey,

     

    maybe you can give measure a try, it assumes that there is a separate Calendar table in your model

    CALCULATE(
		SUM('FactWithDates'[Amount])
		,ALL('Calendar'[Date])
		,DATESBETWEEN('Calendar'[Date],MAXX(VALUES('Calendar'[Date]),'Calendar'[Date]) - 7 , MAXX(VALUES('Calendar'[Date]),'Calendar'[Date])))
)

    Hope this works for you

     

    Regards