Forum Discussion

cv02011's avatar
cv02011
New Member
6 years ago

fact table startdate -enddate

I have a slowly changing dimension and a Fact Table Product Category StartDate EndDate P1 C1 01/01/2020 20/01/2020 P1 C2 21/01/2020 31/03/2020 P2 C3 01/01/2020 31/12/2099 ...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

hi  cv02011 

You could create a measure as below:

Measure =
CALCULATE (
    MAX ( Table1[Category] ),
    FILTER (
        Table1,
        Table1[Product] = SELECTEDVALUE ( Table2[Product] )
            && SELECTEDVALUE ( Table2[Date] ) >= Table1[StartDate]
            && SELECTEDVALUE ( Table2[Date] ) <= Table1[EndDate]
    )
)

Result:

and here is sample pbix file, please try it.

 

Regards,

Lin