Forum Discussion

Boycie92's avatar
Boycie92
Resolver I
9 years ago
Solved

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

Hi all,   I have a problem that Vvelardeand Anonymous 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 ...
Need Help
Show and Tell
  • Boycie92's avatar
    Boycie92
    9 years ago

    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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi Boycie92,

Columns

  1. Headcount
  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.:smileyhappy:

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

 

Regards

Boycie92's avatar
Boycie92
Resolver I
9 years ago

Hi Anonymous

 

Thanks for the reply.

 

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,