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

  1. Headcount
  2. Month = [Date].[MonthNo]

 

Measures:

  1. MinMonth = MINX(ALLSELECTED(Sheet3[Month]),[Month])
  2. MaxMonth = MAXX(ALLSELECTED(Sheet3[Month]),[Month])
  3. maxRowCount = COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),[Date].[MonthNo]=[MaxMonth]))
  4. minRowCount = COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),[Date].[MonthNo]=[MinMonth]))
  5. MinMonthHC = var temp= LASTNONBLANK(Sheet3[Department],Sheet3[Department]) var resultMax= SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MaxMonth]),Sheet3[Headcount]) var resultMin=SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MinMonth]),Sheet3[Headcount]) var currMinCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MinMonth]))) var currMaxCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MaxMonth]))) return if(AND([minRowCount]=[maxRowCount],[maxRowCount]=COUNTAX(VALUES(Sheet3[Department]),[Department]))||AND(currMinCount=currMaxCount,currMaxCount=1),resultMin,if(currMinCount=0,0,resultMin))
  6. MaxMonthHC = var temp= LASTNONBLANK(Sheet3[Department],Sheet3[Department]) var resultMax= SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MaxMonth]),Sheet3[Headcount]) var resultMin=SUMX(FILTER(Sheet3,Sheet3[Department]=temp&&[Date].[MonthNo]=[MinMonth]),Sheet3[Headcount]) var currMinCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MinMonth]))) var currMaxCount=COUNTROWS(FILTER(SUMMARIZE(ALLSELECTED(Sheet3),Sheet3[Department],Sheet3[Date].[MonthNo],"HC",SUM(Sheet3[Headcount])),AND([Department]=temp,[Date].[MonthNo]=[MaxMonth]))) return if(AND([minRowCount]=[maxRowCount],[maxRowCount]=COUNTAX(VALUES(Sheet3[Department]),[Department]))||AND(currMinCount=currMaxCount,currMaxCount=1),resultMax,if(currMaxCount=0,0,resultMax))
  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

 

You can see the original post and more info : http://community.powerbi.com/t5/Desktop/Difference-between-2-columns-in-a-Matrix-Table/td-p/81614

 

Thanks,

Boycie92

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

10 Replies

  • 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