Solved: Difference between 2 columns in a Matrix Table - Page 2

Boycie92 · ‎10-24-2016

Hi all,

I was wondering if someone could help me.

I want to calculate the difference between two columns on a Matrix Table

At row Level I have Department.

At Column Level I have Date. This is an actual Date column within the Data

At Values Level I have headcount. This is a measure and the following formula has been used: DISTINCTCOUNT(‘Employee’[Person ID])

I essentially want to compare the difference in Headcount between two specific dates.

For Example:

April May Difference

Headcount Headcount Headcount

10 9 -1

I have read other blog post and I have tried the following formulas

Previous Month = CALCULATE([HEADCOUNT],FILTER(ALL(‘Employee’[Date].[Month]),MAX(‘Employee’[Date])-1))

Difference = IF(ISBLANK([Previous Month]=0),CALCULATE([Headcount]-[Previous Month]))

By the looks of the results I am getting Mays Data.

When using the difference formula I am getting blank spaces

Can anyone tells me were in going wrong with this?

Thanks

v-shex-msft · ‎11-07-2016

Hi @Boycie92,

It seems like I forget some conditions. You can take a look at below formulas if it works on your side, I upload the pbix file.

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

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

In addition, if above still contain some incorrect part, please provide sample data to test.(my test data is created by random function)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post