Dynamic Last 7 day

[vanessa]

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 sales should be calculated for 7 days prior to 2017/07/18. ie. from 2017/07/11 to 2017/07/18.

Following is the measure I created:

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

The measure selected_Date stores the date selected in the slicer. Following is the formula:

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

The measure 7day_Sales works fine when TODAY() is used instead of selected_Date or even if a hardcoded date is passed.

How can this be implemented?

[Vvelarde]

@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]

@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?

[v-shex-msft]

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]

@v-shex-msft I am trying to create a dynamic measure not column.

[v-shex-msft]

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]

Hi @v-shex-msft

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 

[ebianco]

Hi @Rameshwar,

Were you able to resolve this issue? I'm trying to write a DAX formula involving VAR in Power BI desktop connected to a SSAS Tabular data model and also getting a syntax error when there's actually no error that I can see in my syntax.

Thanks for your input

Esteban

[TomMartens]

Hey,

VAR is not supported in SQL Server 2014 as stated here:

https://msdn.microsoft.com/en-us/library/mt243785.aspx

and also have a look here:

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d...

Regards

[Rameshwar]

Thanks @TomMartens. I was hoping the same. 

Thanks,

Rameshwar

[Rubenvw]

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]

@Rubenvw

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]

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

[vanessafvg]

@vanessa so you saying hardcoded works or doesn't?

are you sure you have the correct date format?

[vanessa]

@vanessafvg hardcoding the value works. Yes the date format is correct.