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
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

vanessa

 

Hi, try with this

 

7day_Sales =

 

VAR selected_Date = IF(HASONEVALUE('Table'[Date]),VALUES('Table'[Date]),TODAY())

RETURN

CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]< [selected_Date] &&'Table'[Date]>= [selected_Date]-7))

vanessa's avatar
vanessa
Post Patron
9 years ago

Vvelarde

the formula works fine when I hardcode the value as below:

7day_Sales =

 VAR selected_Date = IF(HASONEVALUE('Table'[Date]),2017/07/18),TODAY())

RETURN

CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Date]< [selected_Date] &&'Table'[Date]>= [selected_Date]-7))

 

But when I try using VALUES('Table'[Date]) instead hardcoding the date, the measure returns data for all the dates in the table.

 

Is this because the left and right side of the filter condition are hitting the same table?

  • 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