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,

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

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

       

      Regards