topic Re: Dax formula - do not show the last two weeks in visual in DAX Commands and Tips

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

M_MELLEMA — Fri, 02 Feb 2024 10:11:23 GMT

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.

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

lbendlin — Sun, 04 Feb 2024 01:39:31 GMT

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 .

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

Anonymous — Mon, 05 Feb 2024 02:39:46 GMT

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.

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

M_MELLEMA — Mon, 05 Feb 2024 12:49:35 GMT

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?

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

lbendlin — Mon, 05 Feb 2024 13:18:44 GMT

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.

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

M_MELLEMA — Mon, 05 Feb 2024 14:09:09 GMT

I'm using a referencedb already:

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

lbendlin — Mon, 05 Feb 2024 14:38:15 GMT

I don't see a yearweek number column.

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

M_MELLEMA — Mon, 05 Feb 2024 14:49:56 GMT

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

I can create a new column year-weeknumber.

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

lbendlin — Mon, 05 Feb 2024 15:27:18 GMT

yes, that is my recommendation.

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

Anonymous — Tue, 06 Feb 2024 06:30:46 GMT

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.

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

M_MELLEMA — Fri, 09 Feb 2024 09:31:33 GMT

Hi Anonymous

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

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

M_MELLEMA — Wed, 14 Feb 2024 13:26:40 GMT

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.

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

lbendlin — Wed, 14 Feb 2024 13:48:49 GMT

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

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

M_MELLEMA — Wed, 14 Feb 2024 14:00:18 GMT

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?

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

lbendlin — Wed, 14 Feb 2024 14:21:30 GMT

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