Dynamic comparison of values

[Fanis_Georg]

Hello team

I have a table that i have the costs of the materials for the full year broken down by month. Each month that I am running the results i add it in the same excel right under the previous month because the data is not too large and other users are also using it for their analysis. The data structure looks something like below. i want to create a matrix or a table where I compare the current period result vs STD and also the current vs the  Previous Period (CP-1). Obviously the current now is month 6, but then it will change to 7 and so on, that is why i have created this filtering under the column "Period" >> Current, CP-1, CP-2,........ Which is automtaicaly updated as we are progressing during the year. So i would need to have 4 calculated columns, 

1. to show current vs std

2. to show current preriod vs previous period

3. current vs standard percentage

4. Current vs previous period percentage.

Can you please help with the DAX

Material	Costing Result	Period
123456	1,511.22	STD
123456	1,444.64	CP-3
123456	1,444.64	CP-2
123456	1,444.64	CP-1
123456	1,434.44	Current

 Thanks a lot

[v-venuppu]

Hi @Fanis_Georg ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ryan_mayu @Ashish_Mathur @Greg_Deckler for the prompt response.

I have created a PBIX file by using sample data to replicate the scenario.Please go through the attached PBIX file for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly.

Thank you.

[ryan_mayu]

@Fanis_Georg 

do you have date column or other column that will show different period of data? Otherwise how to know which one is the current one?

[Ashish_Mathur]

Hi,

Based on the table that you have shared, show the expected result very clearly.

[Greg_Deckler · Accepted solution]

@Fanis_Georg So perhaps something like this:

Current vs. Standard = 
  VAR __Material = MAX( 'Table'[Material] )
  VAR __TableCurrent = FILTER( ALL( 'Table' ), [ResultPeriod] = "Current" )
  VAR __TableSTD = FILTER( ALL( 'Table' ), [ResultPeriod] = "STD" )
  VAR __Current = SUMX( __TableCurrent, [Costing] )
  VAR __STD = SUMX( __TableSTD, [Costing] )
  VAR __Result = __Current - __STD
RETURN
  __Result

[Fanis_Georg]

@Greg_Deckler  your solution seems to be working but I come across another issue now. When I enter the calculate measure to my matrix i would expect a result like with 4 columns, CP-1, Current, STD, Current vs Standard. Instead i have the following, CP-1, Current vs Standard, Current, Current vs Standard, Standard, Current vs Standard. So basicaly the Current vs Standard calculated measure repeats after each and every column of the matrix.

[Greg_Deckler]

@Fanis_Georg Yeah, that's the joy of working with matrix visuals in Power BI. You have 2 options basically. Make everything a measure, so CP-1, Current, STD you would all make measures and then use all measures for the columns in your matrix. The other way is a custom matrix hierarchy:

The New Hotness (Custom Matrix Hierarchy) - Microsoft Fabric Community

[Fanis_Georg]

@Greg_Deckler  Thank you, indeed. I created new measures for each period and it worked. Thanks