cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Resolver I

Difference between 2 columns in a Matrix Table - DAX Help ASAP

Hi all,

I have a problem that @Vvelardeand @v-shex-msft have been helping me with. They have done such a great job, however I have encountered an issue I need to fix it ASAP.

They have helped me devise a solution that will allow me to compare the Headcount of my company from month to month using a matrix table.

The model:

• Monthly reports are generated and stored in a folder. Power BI has connected to the folder and has merged the data together.
• Each report has a Date column. So for example 1/1/2017 would be assigned to every record within that one monthly report.
• The data is not aggregated into summery tables I am working from actual records

From the help previously given the following formulas were:

Columns

2. Month = [Date].[MonthNo]

Measures:

1. MinMonth = MINX(ALLSELECTED(Sheet3[Month]),[Month])
2. MaxMonth = MAXX(ALLSELECTED(Sheet3[Month]),[Month])
7. Diff = [MaxMonthHC]-[MinMonthHC]

The problem I have is when trying to compare the for December 2016 and January 2017

I need this formulas altered to allow for a comparison between December and January. As the formulas rely on Month number then logically the output is correct. That January (1) is the MInMonthHC and that December is the MaxMonthHC (12). I need a way to swap this reasoning only for this instance. Any help would be greatly appreciated

Thanks,

Boycie92

1 ACCEPTED SOLUTION
Resolver I

Hi all,

In case anyone has a similar issue. there is a pretty simple fix. Instead of Using Month.No I should have used [Date].[Date] in all of the formulas.

I apologies for wasting all of your time on this.

Thanks,

Boycie92

10 REPLIES 10
Employee

Hi @Boycie92,

Columns

2. Month = [Date].[MonthNo]

According to your description, I would suggest you to create a new calculate column called "YearMonth" like below, then use "YearMonth" column instead of "Month" column for the calculation in all your measures mentioned above(keep the logic part), then it should work as expected.

`YearMonth=[Date].[Year]*100 + [Date].[MonthNo]`

Regards

Resolver I

I have used your solution and I have encountered a problem:

•  When comparing later months with each other I am encountering an issue were Departments that are no longer in use show the first headcount (April) figure available.

For example:

Department 1

MinMonthHC = April 2016    MaxMonthHC = September 2016        Diff

200                                         200                                                         0

When it should be

MinMonthHC = April  2016    MaxMonthHC = September  2016       Diff

200                                          0                                                             -200

However it works fine in months when the Department are still active.

It could be the way I have altered the measures? If you have any suggestions I would greatly appreciate it.

Thanks,

Employee

Hi @Boycie92,

Could you share a sample pbix file which can reproduce the issue in this case? So that we can help to investigate this issue. You can upload it to OneDrive or Dropbox and post the link here or just send me the link in private message. Do mask sensitive data before uploading.

Regards

Resolver I

Please accept my apologies for the delay.

I have created a sample file and data set for you.

You can see that when you compare Department 1 from September to December it shows the same figure?
However in actual fact December should have 0 Values?

Thanks again for your time and patience.

https://www.dropbox.com/s/6qqdg5ea02ivtce/New%20Compressed%20(zipped)%20Folder.zip?dl=0

Resolver I

I was wondering if you have had the time to look at the issues I am having?

Thanks,

Boycie92

Employee

Hi @Boycie92,

I am the one to apologise. Sorry for the delay response.

I did spend a lot time looking into your shared pbix file. Then I found the issue may relate to the previous logic of your measures which are a little complex for me to understand. After a few try, I was still not able to fix your issue.

Regards

Resolver I

Thanks for looking into this for me.

I am a little bit confused. Do you know why this error was not showing when I used the Month instead of YearMonth?

Thanks again,

Boycie92

Employee

Hi @Boycie92,

I am a little bit confused. Do you know why this error was not showing when I used the Month instead of YearMonth?

That's the part which confuse me most. Without changing the previous logic, just use YearMonth instead of Month shouldn't make such difference. Sorry, I am also lost on this issue.

Regards

Resolver I

I appreciate your help on this, i'm glad that i'm not the only one that's lost here!

If anyone else in the community has any ideas I would love to hear them?

Kind Regards,

Boycie92

Resolver I

Hi all,

In case anyone has a similar issue. there is a pretty simple fix. Instead of Using Month.No I should have used [Date].[Date] in all of the formulas.

I apologies for wasting all of your time on this.

Thanks,

Boycie92

Announcements

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Power BI Monthly Update - February 2024

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

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors