cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
p-deshmukh
Regular Visitor

Analyzing events with duration | Show value for missing weeks

Hello,

 

I am having trouble to get the Yield Value repeated for a duration of work weeks. 

 

I have two tables: Yield_Fact and Date_Dim. The Yield_Fact table has a Many to One relationship with the Date_Dim table as shown.

Yeild and Date Relationship.PNG

For every Material_Dwid and for a particular duration (which is given by Effective_Begin_Date_Dwid and Effective_End_Date_Dwid) I have a Yield_Qty in percentage. I want to show this Yield_Qty value in a Matrix format in a report such that the value is repeated for all the days between the Begin_Date_Dwid and the End_Date_Dwid at the week level. Currently, I am getting a Yield value only for the Begin Dates. For example, I want to show the value of 1% for all the weeks from WW2020-01 to WW2020-23. WW2020-24 will have a value of 12.1%, which already exists as shown below. 

Week Level Yield.PNG

 

I am attaching Google Drive link to the pbix file which has a sample dataset for one Material_Dwid. (Not able to find a way to upload the pbix file directly to this message. Please copy-paste this link in the browser if the hyperlink is not working).

Link: https://drive.google.com/open?id=1GnwJbSUv3gxrOu8wSeGua2P6HV44-AqP

 

The original data set has more than 1 million records. So the solution should be compatible with such a large data set. Please help. If any additional information is needed, please let me know.

 

Thank you!

Pratik

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Analyzing events with duration | Show value for missing weeks

Hi @p-deshmukh 

working with Start date and End date-scenarios is usually a tricky in Power BI. E.g. in the sample file you have provided, there is a relationship between Yield_fact and date_dim on [date_dwid]=[effective_begining_date_dwid]. In your matrix visual you then only get the weeks where there exists data in 'Yield fact'. If you want to show values for other weeks you would need to create a measure that ignore the relationship, but at the same time maintain the relationship, which is not possible. So to solve this you could delete the relationship between the tables, or set it to inactive, or create a duplicate date_time for this purpose and use this measure:

Qty =
CALCULATE (
    MIN ( Yield_Fact[YIELD_QTY] );
    FILTER (
        Yield_Fact;
        MIN ( Date_Dim[DATE_DWID] ) >= Yield_Fact[EFFECTIVE_BEGIN_DATE_DWID]
            && MIN ( Date_Dim[DATE_DWID] ) <= Yield_Fact[EFFECTIVE_END_DATE_DWID]
    )
)

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

 

View solution in original post

Super User III
Super User III

Re: Analyzing events with duration | Show value for missing weeks

Sorry, forgot to mention that you should replace work_week_name from Date_dim with work_week_name from Date2_dim

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Analyzing events with duration | Show value for missing weeks

Hi @p-deshmukh 

working with Start date and End date-scenarios is usually a tricky in Power BI. E.g. in the sample file you have provided, there is a relationship between Yield_fact and date_dim on [date_dwid]=[effective_begining_date_dwid]. In your matrix visual you then only get the weeks where there exists data in 'Yield fact'. If you want to show values for other weeks you would need to create a measure that ignore the relationship, but at the same time maintain the relationship, which is not possible. So to solve this you could delete the relationship between the tables, or set it to inactive, or create a duplicate date_time for this purpose and use this measure:

Qty =
CALCULATE (
    MIN ( Yield_Fact[YIELD_QTY] );
    FILTER (
        Yield_Fact;
        MIN ( Date_Dim[DATE_DWID] ) >= Yield_Fact[EFFECTIVE_BEGIN_DATE_DWID]
            && MIN ( Date_Dim[DATE_DWID] ) <= Yield_Fact[EFFECTIVE_END_DATE_DWID]
    )
)

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

 

View solution in original post

p-deshmukh
Regular Visitor

Re: Analyzing events with duration | Show value for missing weeks

Hi @sturlaws,

Thank you for your message. 

I created a new table called Date 2_Dim, which is a copy of Date_Dim and used the Date_Dwid fields in the measure formula from the new table. And I replaced the Yield_Qty field with the new measure 'Qty' in the matrix visual, but it is not giving me the desired result. Am I doing it right? Aren't we supposed to use Cross Join in some way?

 

Please find the new pbix file with the solution suggested by you: https://drive.google.com/open?id=1qfYTOGCqfzTam7xgHRmd4reiD_snZurc

 

Please help. Thank you in advance!

Super User III
Super User III

Re: Analyzing events with duration | Show value for missing weeks

Sorry, forgot to mention that you should replace work_week_name from Date_dim with work_week_name from Date2_dim

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors