DAX FILTERS

[Serdet]

Hi All,

I have four columns as per the below example.

Priorty Valves	Required Work Date	Actual Work Date	Percentage Complete
1a	01/01/2021	01/01/2021	100%
1b	05/01/2021	08/01/2021	50%
1c	20/01/2021		0%

I want to show monthly based compliance between what has been worked compared to the required. This calculation will be 

A / B * 100

A = Count of 'Priority Valves' column, filtered by 'Required Work Day'(This Month) & 'Percentage Complete'(100%)
B =  Count of 'Priority Valves' column, filtered by 'Required Work Day'(This Month)

Any ideas on how I can achieve this within DAX?

Many thanks,

Elliot 

[v-yetao1-msft · Accepted solution]

Hi @Serdet 

(1)First you need create a calendar date table .Then create a calculated column to extract the month number for the calendar date .

(2)Then create measures filtered by 'Required Work Day'(This Month) .

A = IF(MONTH(SELECTEDVALUE('Table'[Required Work Date]))=SELECTEDVALUE('Calendar Date'[Month number]) && SELECTEDVALUE('Table'[Percentage Complete])=1 ,1,0)

B = IF(MONTH(SELECTEDVALUE('Table'[Required Work Date]))=SELECTEDVALUE('Calendar Date'[Month number]),1,0)

(3)sum the value return from IF dax .

count A = SUMX('Table',[A])

count B = SUMX('Table',[B])

The final result is as shown :

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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