1

I am unable to achieve the next DAX. I have 2 tables: Revenue and Type.

I want to display: "latest type", at the selected FY in a filter. (That is, if FY 20 is selected, the type shall be the one valid on 31-May-2020).

Revenue table:

enter image description here

Type table:

enter image description here

(Date is dd-mm-year)

When the user selects FY20, I would like to display all Revenue data like:

enter image description here

That is, on 31-May-2020, the valid Type was 3 for customer 15; and Type 2 for customer 492.

(When the user selects FY21, the same shall be displayed since on 31-May-2021, for customer 15 the valid Type was 3 and Type 2 for customer 492. )

When the user selects FY22, I want to display:

enter image description here

(on 31-May-2022, the valid Type was 6 for customer 15) (on 31-May-2022, the valid Type was 2 for customer 492)

and so on...

Is this possible? (I am also concerned performance-wise) (If this cannot be done in DAX, I can do it with a very large table in sql...)

Chicago1988
  • 970
  • 3
  • 14
  • 35
  • Can you explain this part "That is, if FY 20 is selected, the type shall be the one valid on 31-May-2020" how do you estimate this date for FY20 ? – msta42a Sep 07 '21 at 08:03
  • (For this case FY ends 31-may and starts 1-june) So, I have a visible filter on Fiscal Year, so the user can select either 2022, 2021 or 2020. Whatever FY they choose, I pick the last day of the said FY and I would have to fetch the valid Type on that day. – Chicago1988 Sep 07 '21 at 08:33

0 Answers0