How to display correctly a multiplication of two measures that use two distinct dates?

[Anonymous]

I have two measures, productivity and efficiency, which calculates the productivity and efficiency of the mechanicians in our workshop. Productivity is: hours worked / hours available. The efficiency: hours solded / hours worked. There is a third measure of performance, which is nothing more than the multiplication of productivity by efficiency, or hours solded / hours available. However, the chronological (month-to-month) display of productivity and efficiency is based on different dates, where productivity uses the service start date and efficiency uses the service end date. That is, two distinct dates. So when multiplying productivity by efficiency to generate the performance indicator I have to choose one of two dates (start or end service date) to plot the indicator month by month, but the problem is that it gets a wrong number. Eg in September productivity was 61.3% and efficiency was 153.33%, my performance is giving 152.55% using the service end date. Using the service start date gives another value which is also wrong. The correct answer should be 0.613 * 1.5333 = 0.9399 or 93.99% performance for the month of September. How do I normalize these dates to display the correct result of 93.99% on performance indicator?

Power BI File: https://mega.nz/#!HRgHxKzD!7mfp5yOTwkaWYNzQD0IyowZt_61FFwOSealibDx-3wk

[amitchandak]

You can try like this. Join both dates to date dimension. One will active and another one will be inactive. Refer to the link below on how to use userelation to activate the one relation. Now create formulas based on the respective date Service start date and service end date.

https://community.powerbi.com/t5/Desktop/DATE-RANGE-SLICERS-TO-COMPARE-NETSALES-OF-A-DATE-RANGE/td-p...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[lc_finance]

Hi @Anonymous ,

can you share a sample of your data in a Power BI?

You can share it using One Drive, Google Drive or another similar tool.

Regards

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[Anonymous]

Power BI file: https://mega.nz/#!HRgHxKzD!7mfp5yOTwkaWYNzQD0IyowZt_61FFwOSealibDx-3wk

[lc_finance · Accepted solution]

Hi @Anonymous ,

you can find attached the solution Power BI file, showing the correct numbers (93.99% for September desempenho).

And below is a screenshot:

To fix it, I did the following: create a relationship between the Dates table and the 2 tables you used. Then used the Date table for the third chart.

Also- I made sure the same filters were applied on the 3 charts.

Does this help you?

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com