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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.