Show TotalYTD measure in a matrix considering the month of creation of the data

moliveira-GA — Thu, 27 Aug 2020 17:29:45 GMT

Hello!

So, i have this matrix which shows the "Actual + Forecast" sum by month of a year. This measure considers the last month with Actual data and calculates how much rests of the forecast's data until the end of year.

In the forecast's data table, i have a column with the forecast's criation data (now, there are two forecasts, July/2020 and august/2020).

I managed to create a measure that calculates the totalYTD for the measure "Actual + Forecast", as you can see in line SalesRev_Fcst_FY.

But i don't want to show this sum in all months of the matrix, but only in the month which matches with the forecast's month of creation, because I need to visualize the TotalYTD variation between the different version of the forecasts.

As you can see above, the line 'SalesRev_Fcst_FY' matches with the total years sum in the line 'Real + Fcst', which is cool, but that sum was provided by the august/2020 data so, i want it to appears only inside of the 'agosto' (august in portuguese) column. The result should appers like:

Please notice the yellow cells: the total by month matches the totalYTD at july/2020 month (which was the month of creation of the forecast in this case).

This is the table of forecast in the model (the column 'VersaoDados' determines the forecast's creation date):

I use a calendar table related to the others as heads of the matrix.

I've tried to use the 'VersaoDados' as a subhead in the matrix, but te result was the same.

Could you help to figure out a way to do that?

Thanks in advance.

Re: Show TotalYTD measure in a matrix considering the month of creation of the data

Greg_Deckler — Thu, 27 Aug 2020 17:36:50 GMT

moliveira-GA - If I am following this, I think you want this - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428

Re: Show TotalYTD measure in a matrix considering the month of creation of the data

moliveira-GA — Thu, 27 Aug 2020 19:29:47 GMT

Hi Greg_Deckler ,

yes, this idea could help me out I guess. I'm going to try that!

Thanks!

topic Re: Show TotalYTD measure in a matrix considering the month of creation of the data in DAX Commands and Tips

Show TotalYTD measure in a matrix considering the month of creation of the data

Re: Show TotalYTD measure in a matrix considering the month of creation of the data

Re: Show TotalYTD measure in a matrix considering the month of creation of the data