Analyzing events with duration | Show value for missing weeks

[p-deshmukh]

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.

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. 

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

[sturlaws · Accepted solution]

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.

[sturlaws · Accepted solution]

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.

[p-deshmukh]

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!

[sturlaws · Accepted solution]

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