DAX to show percentage difference between 2 months with multiple criteria

[Rob_Morris]

I have a table which lists all jobs that have been recorded in the past year.

This lists each job individually and doesn't present them in a format similar to a pivot table in excel.

Column headings are called   

Job_number, job_kpi, kpi_pass_fail, on_site_time, penalty_points

What I would like to do is create 3 visuals
1) shows the percentage difference between the previous month (based on time in the on_site_time column), and the month before that for specific KPI's (based on the job_kpi field) in terms of the total number of jobs received

2) shows the percentage difference between the previous month (based on time in the on_site_time column), and the month before that for specific KPI's (based on the job_kpi field) in terms of the total number of jobs where kpi_pass_fail field is = "fail"

3) shows the percentage difference between the previous month (based on time in the on_site_time column), and the month before that for specific KPI's (based on the job_kpi field) in terms of the total of the penalty_points field.

I have limited knowledge of DAX and wouldn't know where to start.  All other responses I've seen on here already have the source data in a pivot-table format.

[v-yadongf-msft]

Hi @Rob_Morris ,

In order to better understanding your demands and give the right solution, could you please share with me some screenshots of your data after hiding sensitive information and tell me what's your expected output?

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

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

[amitchandak]

@Rob_Morris , You can use time intelligence for this month vs last month

example

MTD Sales = CALCULATE(SUM(Table[job_kpi]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Table[job_kpi]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Table[job_kpi ),previousmonth('Date'[Date]))

you can filter as need

CALCULATE(SUM(Table[job_kpi]),DATESMTD('Date'[Date]), filter( Table, Table[kpi_pass_fail] = "fail") )

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Rob_Morris]

Thanks for the response @amitchandak 

I'm trying to find a total for the amount of jobs there are (so i think this is a count), and the sum formula keeps throwing and error when i try and load it into the visual.

Error Message:
MdxScript(Model) (9, 42) Calculation error in measure 'Sheet1'[MTD Sales]: The function SUM cannot work with values of type String.