cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## 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 4
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:

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.
New Member

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

Community Support

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.

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors