show blank when calculate previous fiscal year

[Anonymous]

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:

Previous Fiscal sales =

VAR currentFYYear = SELECTEDVALUE ( DimCalendarWeek[Fiscal Year Number])

VAR _PreviousFiscalYear = currentFYYear-1

return

CALCULATE (

        [sales],

        FILTER(DimCalendarYearWeek,

            DimCalendarYearWeek[Fiscal Year ] = _PreviousFiscalYear)

            ,FILTER(DimCategory, DimCategory[Sales Status] = "new")

    )

[Anonymous · Accepted 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.           

[Anonymous]

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?