Forum Discussion
Filtering a calculation group using a slicer with date (weekyear)
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.
Community ChampionIn 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.
123abc , apologies that was my mistake, I fixed the formula but now it is returning blank