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

[Boycie92]

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

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