How to ignore dimension only for LY measures?

[ovonel]

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?)

[v-yangliu-msft · Accepted solution]

Hi  @ovonel ,

I created some data:

Date Table：

Here are the steps you can follow：

1. Create calculated column.

Year = YEAR('Date'[date])

2. Connect the two tables

3. Create measure.

Measure =
var _select=SELECTEDVALUE('ClientType table'[FY])
return
CALCULATE(SUM('Date'[Revenue]),FILTER(ALL('Date'),
'Date'[Year]=_select
))

4. Result:

If it does not meet your expected results, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

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