fact table startdate -enddate

[cv02011]

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

Product	Sales	Date
P1	100	31/01/2020
P1	150	29/02/2020
P1	300	31/03/2020
P2	500	31/01/2020

I'm interested in seeing for a selected date ( in the second table I have only the end of month) the number of products for each category and the sales from the second table.

For example if I choose 31/01/2020 I want to see only Category C2 - sales 100 and Category C3- sales 500 (1 product each).

How can I manage StartDate-EndDate in the first table and assign the correct category for every product of the second table?

Thank you

[v-lili6-msft]

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

[cv02011]

Thank you very much,

but if I have another 2-3 fact table containing product-sales-date and I have to put all informations toghether?

And the dimension doesn't contain only the category, but some attributes also (for example color and so on)--- 

Is it necessary to create a measure for every attribute,including every fact table in the formula?

I don't know the best way to use a slowly changing dimension in a pbix.

Thank you 

[v-lili6-msft]

hi  @cv02011 

You may need to create the relationship among these tables. please refer to this document:

https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

If you still have the problem, Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Greg_Deckler]

Well, with the sample data, you would do this:

Table2 Category = MAXX(FILTER('Table1'[Product] = 'Table2'[Product] && 'Table2'[Date]>='Table1'[StartDate] && 'Table2'[Date]<='Table1'[EndDate]),[Category])