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

[Anonymous]

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.

Class	Worker	Job Profile	Department	Campaign Item	Delivery Date	Due Date	Completion Status	Completion Date
A	Joe	Cleaner	x	A	2/3/2021	3/3/2021	Completed	2/5/2021 0:00
A	Sally	Cleaner	x	A	2/3/2021	3/3/2021	Not Started
A	Bob	Cleaner	x	A	2/3/2021	3/3/2021	Not Started
B	Joe	Cleaner	x	B	3/3/2021	3/3/2021	Not Started
B	Sally	Maintenance Spec	x	B	3/3/2021	3/3/2021	Complete	2/19/2020 7:49
B	Bob	Cleaner	x	B	3/3/2021	3/3/2021	Not Started

[Ashish_Mathur]

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.

[Anonymous]

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.

[negi007]

@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