Previous weeks data

[Marfoss]

Hi, I need a build a dynamic filter so I can select last 4 week, last 8 weeks, last 12 weeks ect ect.

So far I have built one which shows me weeks 1-4, 5- 8 ect using a calculated column but as last 8 weeks also includes last 4 this doesnt seems to work.

Could anyone offer some guidance how to do this?

[v-deddai1-msft]

Hi @Marfoss ,

You can use relative date in slicer:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Marfoss , In your date /week table create a column for week rank and then you can have measure like give below

column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Measure
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Last 4 to 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank]) -4 ))

But if you want trend follow this approach https://www.youtube.com/watch?v=duMSovyosXE

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.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Marfoss]

Hi,

Thanks for this solution however, as far as I understand you can use measures in filters so not 100% sure this gives me what I need?

Thanks

[v-deddai1-msft]

Hi @Marfoss ,

Are you trying to filter value by last8-12 week , last5-8 week and last1-4week? If so, you can try the following measure:

last1-4 = VAR a =  ADDCOLUMNS(SUMMARIZE('Table','Table'[Date],"weeknum",WEEKNUM('Table'[Date])),"filter",VAR b = WEEKNUM(NOW()) return SWITCH(TRUE(),[weeknum] >= b-12&&[weeknum]<b-8,"last9-12",[weeknum] >= b-8&&[weeknum]<=b-4,"last5-8",[weeknum] >= b-4&&[weeknum]<=b-1,"last1-4")) return CALCULATE(SUM('Table'[value]),FILTER(a,[filter] = "last1-4"))

last5-8 = VAR a =  ADDCOLUMNS(SUMMARIZE('Table','Table'[Date],"weeknum",WEEKNUM('Table'[Date])),"filter",VAR b = WEEKNUM(NOW()) return SWITCH(TRUE(),[weeknum] >= b-12&&[weeknum]<b-8,"last9-12",[weeknum] >= b-8&&[weeknum]<=b-4,"last5-8",[weeknum] >= b-4&&[weeknum]<=b-1,"last1-4")) return CALCULATE(SUM('Table'[value]),FILTER(a,[filter] = "last5-8"))

last9-12 = VAR a =  ADDCOLUMNS(SUMMARIZE('Table','Table'[Date],"weeknum",WEEKNUM('Table'[Date])),"filter",VAR b = WEEKNUM(NOW()) return SWITCH(TRUE(),[weeknum] >= b-12&&[weeknum]<b-8,"last9-12",[weeknum] >= b-8&&[weeknum]<=b-4,"last5-8",[weeknum] >= b-4&&[weeknum]<=b-1,"last1-4")) return CALCULATE(SUM('Table'[value]),FILTER(a,[filter] = "last9-12"))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Marfoss]

Thanks however, as I understand measures cant be used as filters so not sure this will help?

[v-deddai1-msft · Accepted solution]

Hi @Marfoss ,

Measure can only be set as  visual level filter .  First create a calculated column in your fact table：

weeknum = WEEKNUM('Table'[Date]))

You can create the following measure and add it to visual level filter:

measure = VAR b = WEEKNUM(NOW()) return SWITCH(TRUE(),[weeknum] >= b-12&&[weeknum]<b-8,"last9-12",[weeknum] >= b-8&&[weeknum]<=b-4,"last5-8",[weeknum] >= b-4&&[weeknum]<=b-1,"last1-4")

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai