Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi All, I have a calendarweek table where they a fiscal year column , have a fiscal year filter and the user should see the selected year and previous fiscal year sales in a table, got blank when it shows previous years sales, my measure:
Solved! Go to Solution.
Hi @Anonymous ,
The solution is to create a separate table with the field [Fiscal Year Number]. The Fiscal Year Number field of the new table is then filtered as a slicer field. Below is the solution steps based on simple sample data.
For example, if I select 2022, it should display data for 2021.
1.Create a separate table.
Table = DISTINCT('DimCalendarWeek'[Fiscal Year Number])
2.Create a slicer, the field is from the new separate table.
3.Create a measure. The fields in SELECTEDVALUE() function need to be modified to the fields of the new table. Nothing else needs to be changed, but I don't have a DimCategory table in the example, so I changed it.
Previous Fiscal sales =
VAR currentFYYear = SELECTEDVALUE ('Table'[Fiscal Year Number])
VAR _PreviousFiscalYear = currentFYYear-1
return
CALCULATE (
SUM(DimCalendarWeek[Sale]),
FILTER('DimCalendarWeek',
'DimCalendarWeek'[Fiscal Year Number] = _PreviousFiscalYear)
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
The solution is to create a separate table with the field [Fiscal Year Number]. The Fiscal Year Number field of the new table is then filtered as a slicer field. Below is the solution steps based on simple sample data.
For example, if I select 2022, it should display data for 2021.
1.Create a separate table.
Table = DISTINCT('DimCalendarWeek'[Fiscal Year Number])
2.Create a slicer, the field is from the new separate table.
3.Create a measure. The fields in SELECTEDVALUE() function need to be modified to the fields of the new table. Nothing else needs to be changed, but I don't have a DimCategory table in the example, so I changed it.
Previous Fiscal sales =
VAR currentFYYear = SELECTEDVALUE ('Table'[Fiscal Year Number])
VAR _PreviousFiscalYear = currentFYYear-1
return
CALCULATE (
SUM(DimCalendarWeek[Sale]),
FILTER('DimCalendarWeek',
'DimCalendarWeek'[Fiscal Year Number] = _PreviousFiscalYear)
)
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks , that works fine. I have another issue, now i want to use the same filter (fiscal year) but i want display year month that belongs to that fiscal year, how should i do?
User | Count |
---|---|
121 | |
69 | |
66 | |
56 | |
52 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |