Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Help! How to show Training Completion % over time. Need help with measure and model.

Please help! New to Power BI.

I need to show training completion % over time. 

I do have a Calender table and a Fact Training table.

My Training fact table column structure consists of:

I assume I need to link my COMPLETION DATE to my DATE in my calender table, but I'm stuck after that step.

My new measure TRAINING COMPLETION % will be Completed Trainings / Total Trainings but I need logic to ignore trainings with DUE DATE greater than Max calender date?

 

End goal is to have a line chart with either Month or Week across X axis, and Training compliance Line across Y axis.

Need help on the DAX measure of TRAINING COMPLETION %, ideally with logic ignore Due Dates greater than the current date in calender. 

 

Please help! Also do i need a active relationship or inactive relation ship between the fact table and date table, and which fields.

I'm a newbie so please be detailed as possbile. Preferably DAX solutions.. im not good with advanced qry.

 

 

ClassWorkerJob ProfileDepartmentCampaign ItemDelivery DateDue DateCompletion StatusCompletion Date
AJoeCleanerxA2/3/20213/3/2021Completed2/5/2021 0:00
ASallyCleanerxA2/3/20213/3/2021Not Started 
ABobCleanerxA2/3/20213/3/2021Not Started 
BJoeCleanerxB3/3/20213/3/2021Not Started 
BSallyMaintenance SpecxB3/3/20213/3/2021Complete2/19/2020 7:49
BBobCleanerxB3/3/20213/3/2021Not Started 

 

 

 

Need Help

3 Replies

  • negi007's avatar
    negi007
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous You can have a measure like below to calculated completed training status 

     

    Completion_Rate_N =
    var max_dt = MAX('Calendar'[Date])
    var tol_training =CALCULATE(COUNT(Completion[Class]),Completion[Due Date]<=max_dt)
    var compl_train = CALCULATE(COUNT(Completion[Class]),Completion[Completion Status]="Completed",Completion[Due Date]<=max_dt)
    return
    DIVIDE(compl_train,tol_training,0)
     
    Please ensure that in your date tables, the max date value should not be higher than due date. To ensure it while creating calendar tables. You could do the below
    Calendar = CALENDAR(FIRSTDATE(Completion[Delivery Date]),LASTDATE(Completion[Due Date]))
     
    here is the output, you are looking for.

     

    I have attached powerbi file for your help as well
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _com=CALCULATE(COUNT('Table'[Completion Status]),FILTER(ALLSELECTED('Table'),'Table'[Completion Status]="Completed"))

var _all=COUNTX(ALL('Table'),'Table'[Completion Status])
return
IF(MAX('Table'[Due Date])<=MAXX(ALL('date'),'date'[Date]),DIVIDE(_com,_all),BLANK())

    2. Place Date in Axis, Completion Status in Legent, and Measure in Values

    3. Result.

     

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    In the simple table that you have shared, what should the figure be for the denominator i.e. total trainings?  If you wanto to ignore all rows where due date > last date of today's month i.e. 28 Feb 2021, then the denominator will be 0.