topic Re: Slicer updating custom made week by the current week in DAX Commands and Tips

Slicer updating custom made week by the current week

Anonymous — Thu, 24 Mar 2022 16:58:15 GMT

Hi, I have a report which is showing weekly stats based on a custom made week. In our company the weeks goes from Friday to Thursday.
I have a custom made week column which looks like this:

Week+Dates = 'Date'[Week of Year(Fri-Thu)!] &": (" & 'Date'[WeekBetweenDates] &")"
I.E for this current week its week 12 and goes from 18th of march until 24th of march. Appears like this: 12: (18.03 - 24.03)
Im using this logic in a slicer and it works great.

To my issue:
Tomorrow it will be a new week: 13: (25.03 - 31.03) and I want my report to change to this automaticly when entering the report, so I dont have to select this manually each week.

Is this something that can be solved?

Appriciate any help!
Thank you.

Re: Slicer updating custom made week by the current week

Whitewater100 — Thu, 24 Mar 2022 18:54:27 GMT

Hi:

You can have two calculated columns, I put thrm in Date Table.

Week Start (Fri) = Dates[Date] - WEEKDAY(Dates[Date],2)+5

Week End (thur) = Dates[Date] - WEEKDAY(Dates[Date],2)+11

and then you can have slicer. Please see below..

Re: Slicer updating custom made week by the current week

Whitewater100 — Thu, 24 Mar 2022 19:17:08 GMT

I beleive these columns would work ( to produce results against data)only if they are in your fact table. I just tested. on a different week start. The calculated columns initially given for Fri-Tursday would work for your purpose.

Re: Slicer updating custom made week by the current week

Anonymous — Fri, 25 Mar 2022 07:50:22 GMT

Whitewater100 hi, I dont understand how this will solve my issue with updating the slicer to the current week automaticly?
I already have the custom weeks in my report in a slicer:

The problem is how this can be 13: (25.03 - 31.03) without touching it 🙂

Re: Slicer updating custom made week by the current week

Whitewater100 — Fri, 25 Mar 2022 12:53:56 GMT

Hi:

Sorry I misunderstood. You can put a calculated column in your fact table and use it as the slicer. But first add WeekNum to your fact table. You can do that in PQ or in DAX.

Current Week in Fact Table = IF(FactTable[WeekNum] = MAX(FactTable[WeekNum]), "Current", CONVERT(FactTable[WeekNum], STRING))

Current Week in Date Table CC =

Curr Week =

var cweek = WEEKNUM(TODAY())

return

IF(Dates[Week No.] =cweek,

"Current", CONVERT(Dates[Week No.],STRING))

Use this for your slicer and select current week from filter pane and it will always open to current week.

I beleive this will also work if you put this Current Week Calc Col in the Dates Table and follow the same steps.

If you'd like, if you share some sample data I can show this technique.

Thanks..

Re: Slicer updating custom made week by the current week

Anonymous — Mon, 28 Mar 2022 19:19:57 GMT

Hi, thank you Whitewater100 ,
The problem with the column now is that it shows the next week as "current":

The formula looks like this:

Current week =

var cweek = WEEKNUM(TODAY())

return

IF('Date'[Week of Year(Fri-Thu)!] = cweek,

"current",CONVERT('Date'[Week of Year(Fri-Thu)!],STRING))

Where ('Date'[Week of Year(Fri-Thu)!] is the modified week number (format in Whole numbers).

Any idéas?

I would love to share a sample in pbix but im not sure how to post it here.

Re: Slicer updating custom made week by the current week

Whitewater100 — Mon, 28 Mar 2022 19:32:12 GMT

Hi Kiisj:

It might be something about your date table.

This usually is an easy fix..

If you go to Google Drive and where it says MY Files, you can choose Upload.

On the right side the menu has "Recents". You can Rclick on your file and it will ask if you want a link to share. Sometimes on the dropdown you choose "anyone with this link" to allow others to see. Of course if it's not private.

I hope this helps!

Re: Slicer updating custom made week by the current week

Anonymous — Tue, 29 Mar 2022 02:26:17 GMT

Hi Anonymous ,

Because the values of table column is always fixed, they won't be automatically changed everyday. Also, we are not able to add measures into a slicer.

To work around your requirement, you could create two measures and a calendar table as below:

Week start = TODAY()-WEEKDAY(TODAY(),2)+1

Week End = TODAY()-WEEKDAY(TODAY(),2)+7

Date Table = CALENDAR([Week start],[Week End])

Add 'Date Table'[Date] into slicer.

Then create relationship between the tables.

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

Re: Slicer updating custom made week by the current week

Anonymous — Tue, 29 Mar 2022 08:52:52 GMT

Whitewater100 Ok nice, here is the link to the report:
https://drive.google.com/file/d/1a1CikvXnR3nnWl-pWlLhsIZOEYZWv8-o/view?usp=sharinghttps://drive.google.com/file/d/1DyKclo-COPFX6NdFh6QYeopR3SlXvsuP/view?usp=sharing

Re: Slicer updating custom made week by the current week

Anonymous — Tue, 29 Mar 2022 08:54:57 GMT

Anonymous Hi see this link to the test report
https://drive.google.com/file/d/1a1CikvXnR3nnWl-pWlLhsIZOEYZWv8-o/view?usp=sharinghttps://drive.google.com/file/d/1a1CikvXnR3nnWl-pWlLhsIZOEYZWv8-o/view?usp=sharing

My desired outcome is that the report will move automaticly to Week no 14 when first of april starts:

Re: Slicer updating custom made week by the current week

Whitewater100 — Tue, 29 Mar 2022 13:23:25 GMT

Hi:

I beleive you may already have a way for week to begin on Friday. Just in case, this is less code:

Week Start Fri = WEEKNUM(TableName[Date],15)

For the current week slicer the below noramlly works

You can put a calculated column in your fact table and use it as the slicer. But first add WeekNum to your fact table. You can do that in PQ or in DAX.

Current Week in Fact Table = IF(FactTable[WeekNum] = MAX(FactTable[WeekNum]), "Current", CONVERT(FactTable[WeekNum], STRING))

Current Week in Date Table CC =

Curr Week =

var cweek = WEEKNUM(TODAY())

return

IF(Dates[Week No.] =cweek,

"Current", CONVERT(Dates[Week No.],STRING))

Use this for your slicer and select current week from filter pane and it will always open to current week.

I believe this will also work if you put this Current Week Calc Col in the Dates Table and follow the same steps.

Re: Slicer updating custom made week by the current week

Anonymous — Wed, 30 Mar 2022 10:59:02 GMT

Whitewater100 Hi, thanks. Im not able to make this work, I get the same outcome as last time. Could you please show in the file that I shared?

Re: Slicer updating custom made week by the current week

Whitewater100 — Wed, 30 Mar 2022 11:45:25 GMT

Hi:

OK, I will try that. I will look asap, after some meetings today. Hope that is OK. Thanks

Re: Slicer updating custom made week by the current week

Whitewater100 — Wed, 30 Mar 2022 17:01:31 GMT

Hi Kiisj:

You did an awesome job making the calendar. I just subtracted a 1 to get current week. I have put that as a filter in the filter panel. The other thing is I disconnected the auto date time as there were several date tables running in the background. I wasn't sure if you wanted to change the first date to 12-30-2021 or leave it, so I left it.

I hope this was helpful as you did most all the work! Thanks..

https://drive.google.com/file/d/16X0PLjZClrQSZlgNEdVfGVfj53YQbXPr/view?usp=sharing