cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cv02011
New Member

fact table startdate -enddate

I have a slowly changing dimension and a Fact Table

ProductCategoryStartDateEndDate
P1C101/01/202020/01/2020
P1C221/01/202031/03/2020
P2C301/01/202031/12/2099

 

ProductSalesDate
P110031/01/2020
P115029/02/2020
P130031/03/2020
P250031/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

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

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:

4.JPG

and here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.