Converting Excel to DAX

[Sethforte1]

Hello All,

I'm not sure if this is as hard as I am making it out to be, but I keep striking out. I have been tasked with converting this Excel Dashboard to a PowerBI report and the sheet on "QTC AVG Completion" is just throwing me off beyond measure. Still fairly new to PowerBI and quite frankly still fairly new to Excel and Vlookups and Power Pivots.

Can someone please assist me with a DAX formula 

to produce the graph I am seeing on the excel dashboard? Right now all I am able to do is calculate the sum of the completed assignments by month and show that. I can tell there is a lot of back end dynamic excel funcitions happening on the "Assignment Analysis" tab to produce their "QTC AVG Completion" graph. I have attached the excel sheet and copy of the Power BI report in a google drive for reference. 

https://drive.google.com/drive/folders/1aH1MKakjajjdsDuO5380vrTKH2P-_SnF?usp=sharing

[MFelix · Accepted solution]

Hi @Sethforte1 ,

First of all your explanation is not the best one and your dataset is very complex to achieve any results withouth understanding what you want to have.

As a good practice check 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/38490 (courtesy of @Greg_Deckler).

Picking up your two table you refer I have made the following measure:

Measure =
DIVIDE (
    COUNT ( tbl_Import[Complete] );
    CALCULATE (
        SELECTEDVALUE ( tbl_DaysCount[Working Days-Holidays] );
        FILTER (
            tbl_DaysCount;
            tbl_DaysCount[Month] = MAX ( tbl_Import[Complete Month] )
        )
    )
)

This is not the optimal measure and need further explanation to help you better, and believe that the first step is to get a better data model in place.

Check the PBIX file with the chart and if it's working properly, if you can also give better insights would be helpfull