topic Re: Filtering a calculation group using a slicer with date (weekyear) in DAX Commands and Tips

Filtering a calculation group using a slicer with date (weekyear)

Anonymous — Thu, 26 Oct 2023 12:15:09 GMT

Hi guys,

I have created a model that is using calculation groups: Act,LW,LY

My formulas for the calculation groups are

CALCULATE( SELECTEDMEASURE(), YEAR(CalendarTable[Date]) = YEAR(TODAY()) && WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()) - 1 )
CALCULATE( SELECTEDMEASURE(), YEAR(CalendarTable[Date]) = YEAR(TODAY()) && WEEKNUM(CalendarTable[I]) = WEEKNUM(TODAY()) - 2 )
CALCULATE( SELECTEDMEASURE(), YEAR(CalendarTable[Date]) = YEAR(TODAY())-1 && WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()) - 1 )

I have a measure called lets say Sales = sum(salestable[sales])

I've build a matrix visual and dragged the measure under values and calculation groups under columns which works absolutely perfect.

What I would like to be able to do is to create a slicer with the weekyear that would filter my matrix table

So if i was to select current weekyear the actual should be filtered to the actual week but if i select a different weekyear the actual will change based on the weekyear selection.

Any idea if this is possible?

Thanks in advance

Re: Filtering a calculation group using a slicer with date (weekyear)

123abc — Thu, 26 Oct 2023 12:32:43 GMT

In Power BI, you can achieve your desired outcome by creating a slicer based on the "WeekYear" and then using that slicer to filter your matrix visual. Here's how you can do it:

Create a WeekYear Slicer:
- Create a new slicer in your report.
- Add the "WeekYear" field to the Values section of the slicer.
- Customize the slicer as needed to make it user-friendly.
Update your Calculation Groups:
You already have calculation groups for "Act," "LW" (Last Week), and "LY" (Last Year). You'll need to update these calculations to consider the selected "WeekYear" in the slicer. You can use the FILTER function to achieve this. Here's an example of how to modify your calculation groups:
- For "Act" (Actual):

CALCULATE(
SELECTEDMEASURE(),
YEAR(CalendarTable[Date]) = YEAR(TODAY()) && WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()),
FILTER(ALL('YourDateTable'), 'YourDateTable'[WeekYear] = SELECTEDVALUE('YourWeekYearSlicer'[WeekYear]))
)

For "LW" (Last Week):

CALCULATE(
SELECTEDMEASURE(),
YEAR(CalendarTable[Date]) = YEAR(TODAY()) && WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()) - 1,
FILTER(ALL('YourDateTable'), 'YourDateTable'[WeekYear] = SELECTEDVALUE('YourWeekYearSlicer'[WeekYear]))
)

For "LY" (Last Year):

CALCULATE(
SELECTEDMEASURE(),
YEAR(CalendarTable[Date]) = YEAR(TODAY()) - 1 && WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()) - 1,
FILTER(ALL('YourDateTable'), 'YourDateTable'[WeekYear] = SELECTEDVALUE('YourWeekYearSlicer'[WeekYear]))
)

Replace 'YourDateTable' with the name of your date table, and 'YourWeekYearSlicer' with the name of your WeekYear slicer.
Use the WeekYear Slicer:
- Place the WeekYear slicer on your report canvas.
- When you select a specific WeekYear from the slicer, your matrix visual should automatically update based on the WeekYear selection, reflecting the calculations for the selected WeekYear.

This way, your matrix visual will dynamically respond to the WeekYear slicer's selection, and your calculations will take the selected WeekYear into account.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Filtering a calculation group using a slicer with date (weekyear)

Anonymous — Thu, 26 Oct 2023 12:58:21 GMT

123abc thank you for your quick response, but I am getting the following error

Expression: A single value for column 'WeekYear' in table 'CalendarTable' cannot be determined

when using this formula

CALCULATE( SELECTEDMEASURE(), YEAR(CalendarTable[Date]) = YEAR(TODAY()) && WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()), FILTER(ALL(CalendarTable[Date]), CalendarTable[WeekYear] = SELECTEDVALUE([WeekNum])) )

WeekYear is a column in my CalendarTable

Re: Filtering a calculation group using a slicer with date (weekyear)

Anonymous — Thu, 26 Oct 2023 13:06:05 GMT

123abc , apologies that was my mistake, I fixed the formula but now it is returning blank

Re: Filtering a calculation group using a slicer with date (weekyear)

123abc — Thu, 26 Oct 2023 13:14:06 GMT

I apologize for the oversight. It seems there's a minor issue with your DAX formula. The error you're encountering is because it's having trouble determining a single value for 'WeekYear' in the context of your calculation.

To resolve this issue, you can slightly modify your DAX formula. You should use the VALUES function to retrieve the distinct weekyear values for the selected context. Here's the updated formula:

CALCULATE(
SELECTEDMEASURE(),
YEAR(CalendarTable[Date]) = YEAR(TODAY()) &&
WEEKNUM(CalendarTable[Date]) = WEEKNUM(TODAY()),
FILTER(ALL(CalendarTable), CalendarTable[WeekYear] = SELECTEDVALUE(WeekyearTable[Weekyear]))
)

In this formula, we're using SELECTEDVALUE(WeekyearTable[Weekyear]) to retrieve the selected weekyear from your slicer. This should resolve the error and correctly filter your calculation based on the selected weekyear.

Make sure that you have a 'WeekyearTable' with the 'Weekyear' column connected to your slicer, and it contains distinct weekyear values corresponding to your 'CalendarTable'.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Filtering a calculation group using a slicer with date (weekyear)

Anonymous — Thu, 26 Oct 2023 14:09:24 GMT

123abc , THANK YOU!

I had to make a few adjustments to the formula you provided and change my CalendarTable a bit but now it works perfectly!

Thank you!