Dax formula - do not show the last two weeks in visual

[M_MELLEMA]

Hi,

I have a visual with weeks and hours:

 I don't want to show the last two weeks in the visual. So in this case I only want to see week 1,2,3 not week 4 and 5.

weeknr is a created column: 

I've tried a couple things but I'm not an experted to DAX. So I hope someone can help me.

[Anonymous]

Hi @M_MELLEMA ,
You can try changing the previous metric to something like the following, which will dynamically remove the last two weeks.

Measure = 
VAR max_week = CALCULATE(MAX('Table'[weeknr]),ALL('Table'))
return
IF(MAX('Table'[weeknr]) <= max_week - 2,MAX('Table'[value]),BLANK())

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[M_MELLEMA]

Hi @Anonymous 

I've tried this measure and put it in the values of the visual. But I still see all weeks.

[M_MELLEMA · Accepted solution]

Solution:

Add a column Currentweek = WEEKNUM(today())

Add another column Notlasttwoweeks = IF((Datumtabel[Huidige_week]-Datumtabel[weeknr])<2,1,0)

Put the column Notlasttwoweeks in the filter of the visual.

[lbendlin]

That won't work in the first two weeks of January.

[M_MELLEMA]

I think that when it's week 1 or week 2, the user doesn't see any results. But I have a button that will show all weeks.

What do you suggest? 

[lbendlin]

Use YearWeek instead of Week number, and then TOPN(2) over all yearweeks that are smaller than the current value.

[Anonymous]

Hi @M_MELLEMA ,

1.you can create a measure value by writing the following expression.

Measure = IF(MAX('Table'[weeknr]) < 4,MAX('Table'[value]),BLANK())

2.The result obtained is shown below.

You can also choose not to use measure. You can do this by going to filters on the right hand side of the report, selecting the weeknr column you want to change, making the value less than or equal to 3, and then selecting Apply filter.

You can also change the Filter type to Top N, drag the data to be filtered into By value, and select Apply filter.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[M_MELLEMA]

Hi Clara,

The filter is working, but I want it dynamically.  Whatever week it is, I don't want to see the last 2 weeks.

Let's say it's week 10, then I don't want to see weeks 9 and 10. Let's say it's week 14, then I don't want to see weeks 12 and 13.

I've tried the measure: 

that's working to, but is also not dynamically.  How can I make it dynamically?

[lbendlin]

How would that work in the first week of the new year?

You need a "YearWeek" number,  and this is where stuff gets weird very fast. Weeks are incompatible with years and months.  Your only true solution is an external reference table where YOU define what the week number is for each day of each year.  

- grab TODAY()'s yearweek number

- find all yearweek numbers smaller than that, sort descending

- chop off the TOPN2

- use the rest as filter.

[M_MELLEMA]

I'm using a referencedb already:

[lbendlin]

I don't see a yearweek number column.

[M_MELLEMA]

You're right. This are the field that are available in our referencedb:

I can create a new column year-weeknumber.

[lbendlin]

yes, that is my recommendation.

[lbendlin]

I recommend you use an externally sourced calendar table that already has the week numbers prepopulated.

Create a measure filter that excludes dates greater than TODAY()-14 .