Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rob_Morris
Helper I
Helper I

DAX to show percentage difference between 2 months with multiple criteria

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.

 

3 REPLIES 3
v-yadongf-msft
Community Support
Community Support

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
Super User
Super User

@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

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.

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.