Filter based on two date dimension

[ss]

I have 2 date dimensions

• Accounting Period
• Forecast Period

And [Revenue Data] table with measures

Problem:

In the following screenshot I have filtered (slicer) on Accounting Period 2017 Mar, this will list all the Forecast Period (basically each Accounting Period will have 12 Forecast Periods of same year) and the YTD measure

  

The following is exactly the same but I have filtered (slicer) on Accounting Period 2017 Jun

I wanted [YTD Last Quarter] measure - I used PREVIOUSQUARTER function

 

YTD Last Quarter gives me, for example, In 2017 Jun Accounting Period as filtered in slicer - The Revenue YTD of 2017 Jun Forecast Period as in 2017 Mar Accounting Period (Mar is the previous quarter). So far all good, I get the correct number

Now I just need to get that particular line alone in the visual (as highlighted above with arrow). I thought it was as simple as adding a calculated column IF(Accounting Period = Forecast Period, TRUE(), FALSE() ) and then add that slicer.

But Calculated column isn't working as you can see in below screenshot.

• For YTD it filters on 2017 Jun because that’s True
• For YTD Last Quarter it filters on 2017 Mar - because in 2017 Mar Accounting Period that was True

 

 I tried to create relationship between Accounting Period and Forecast Period but ended up with ambigous path error.

My desired result is following - somehow just need to get that line alone where Accounting Period = Forecast Period with respective measures

 

Any idea please - Thank you.

[v-shex-msft]

Hi @ss,

I'm not so clear for your table structure, relationship and formula, can you please share some sample data for testing and coding formula?

Regards,

Xiaoxin Sheng