Forum Discussion
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 |
| 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
4 Replies
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
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/38490Regards,
Lin
- 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])
