Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
moliveira-GA
Frequent Visitor

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

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.

 

Tabela.JPG

 

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:

 

Tabela_2.JPG

 

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

 

dados.JPG

 

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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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/...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@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/...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler ,

 

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

 

Thanks!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.

Top Solution Authors