How to ignore dimension only for LY measures?

[Anonymous]

I am trying to build a report… I have a fact with Revenue and dimensions: Date, Client, ClientType.

ClientType table:

CustomerNo	FY	Type
1	2018	xyz
1	2019	xyzz
1	2020	xyzz
1	2021	xyzz2
2	2018	xyz
2	2019	xyz
2	2020	xyz
2	2021	xyz2

For each Year, each customer has a Type.

In the report, a single-selection filter on FY will be included.

My goal is, if FY 2021 is selected:

•  Revenue for FY 2021 should be displayed.
• The Type for FY 2021 should be displayed.
• I have one LY measure on revenue that should display values under type from FY2021, not FY2020!

Likewise if FY 2020 is displayed, the LY  measures, shall display under the type of FY 2020.

Can the above be achieved? Can/shall I connect Date table with ClientType (even though they have different granularity?)

[Anonymous]

Hi @Anonymous ,

Now that you have the FY column, put the FY column into the slicer as a filter. Then LY measure, you can modify it to

New LY measure=CALCULATE([LY measure], ALLSELECTED('ClientType'))

If the solution is not successful, please complete the sample data and give the corresponding expected results. I will try my best to help you.

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.

[amitchandak]

@Anonymous , Just like your revenue FY create a max(Table[Type])  for FY

calculate(max(Table[Type]),datesytd(Date[Date])

or

CALCULATE(max(Table[Type]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))

or

Last year =

var _max  = maxX(allselected('Date'), 'Date'[Year])

return

CALCULATE(max(Table[Type]),filter(ALL('Date'),'Date'[Year]=_max))

[Anonymous]

I dont think I can't do that, I am consuming from a SSAS cube, so the tables are the ones I receive...