PowerBI Calculation Group to Return a Specific month value for Last Year Month

[KRunk02]

Hello - 

I have a calculated group that I labeled as "KPI" I want these to be additional TOTAL Measures that show in a matrix after the base value today. I have the matrix set up where my category Hierarchy is on the rows and my KPI's (calculation group) and Month are in the columns.  

I am trying to create some additional KPI measures with one being Last Year Current Month (so if its September 2024, it will return September 2023 values but just that month. I got the formula to work but it will only work if you select September 2023 In the date slicer which is not what I want. This is the formula that I used: 

IF( NOT HASONEVALUE('GCPDate_tbl'[date_id]), VAR Latest_CY_Month = MAX(FactRevenueTable[MonthID]) VAR Latest_LY_Month = YEAR(Latest_CY_Month) - 1 VAR Current_Month = MONTH(Latest_CY_Month) // Get the specific month VAR Result = CALCULATE( SELECTEDMEASURE(), KEEPFILTERS(ALLEXCEPT(FactRevenueTable, FactRevenueTable[PatType])), FILTER( 'GCPDate_tbl', YEAR('GCPDate_tbl'[MONTHID MMMM-YYYY]) = Latest_LY_Month && MONTH('GCPDate_tbl'[MONTHID MMMM-YYYY]) = Current_Month // Add month filter ) ) RETURN DIVIDE(Result, 1000000), BLANK() )

I also need help with the Variance $ and the YOY % Variance if someone can help. No matter what I've tried it either won't show as a total column or returns the wrong amount. 

[KRunk02]

This is what it returned. which is correct that I want to return September 2023. Here is a better picture of the dashboard set up where I am trying to get the formula's to work correctly. You can see that I have the available dates in a slicer, the KPI's that I want to be able to only show as a TOTAL Column beside the selected Month totals as comparison which is why I just want to be able to show Last Year Month. You can see that I have Report Month calculated and I am getting the correct LYMonth amount HOWEVER, It will only show if I select it in the month slicer which is not what I want. I want it to show regardless of the month slicer. Thanks for the help. 

[hnguy71]

Looks like you're missing an ALL evaluation somewhere. This may work for you:

	VAR Latest_CY_Month = CALCULATE(MAX(FactRevenueTable[MonthID]), ALLSELECTED(FactRevenueTable[MonthID]))
	VAR Latest_LY_Month = YEAR(Latest_CY_Month) - 1 
	VAR Current_Month = MONTH(Latest_CY_Month) // Get the specific month 


	VAR Result = CALCULATE( SELECTEDMEASURE(), KEEPFILTERS(ALLEXCEPT(FactRevenueTable, FactRevenueTable[PatType])), FILTER( ALL('GCPDate_tbl'), YEAR('GCPDate_tbl'[MONTHID MMMM-YYYY]) = Latest_LY_Month && MONTH('GCPDate_tbl'[MONTHID MMMM-YYYY]) = Current_Month // Add month filter ) ) 

[KRunk02]

Thank you for the reply. This didn't work. I was thinking this morning, what if we tried a MTD calculation? would that return the sameperiodlastyear if we said CY MTD = selectedvalues then used the sameperiodlastyear? 

[hnguy71]

Hi @KRunk02 

Sorry to hear that.

Is there a pbix that you can share or a sample model that describes your issue? The calculation itself is very atypical so I'm surprised that it isn't working. 

[hnguy71]

Hi @KRunk02 ,

It's hard to tell without knowing what the output of your variables are. A great idea is to create a new measure and drop all your variables to see what are the values being returned. Here's a simple trick that I typically use for debugging:

Debug :=
VAR Latest_CY_Month = MAX(FactRevenueTable[MonthID]) 
VAR Latest_LY_Month = YEAR(Latest_CY_Month) - 1 
VAR Current_Month = MONTH(Latest_CY_Month) // Get the specific month 

RETURN

Latest_CY_Month & " | " & Latest_LY_Month  & " | " & Current_Month 

Drop that in either the same visual or a new visual and send back the results. Another thing I noticed was that you're not using the ALL function to clear any filter context on the date table, that might also be an issue.