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
Anonymous's avatar
Anonymous
Not applicable
9 years ago

HI vanessa,

 

Power bi not support dynamic calculated columns based on slicer, you can create dynamic measure based on slicer.

In addition, you can use query parameter to create a dynamic calculated column on power query.

Deep Dive into Query Parameters and Power BI Templates

 

Regards,

Xiaoxin Sheng

vanessa's avatar
vanessa
Post Patron
9 years ago

Anonymous I am trying to create a dynamic measure not column.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi vanessa,

     

    You can try to use below formula to calculate the last 7 day sales:

    Last 7 Day Sales = 
var seleted=LASTDATE(ALLSELECTED('Fact table'[Date]))
return
CALCULATE(SUM('Fact table'[Sales]),FILTER(ALL('Fact table'),'Fact table'[Date]>=seleted-7&&'Fact table'[Date]<=seleted))

     

     

    Regards,

    Xiaoxin Sheng

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