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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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



Follow on LinkedIn
@ 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!:
Power BI Cookbook Third Edition (Color)

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors