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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to replicate this table in a power bi matrix

Hi, how are you? I need to put together a power bi matrix and I have no idea how to do it.
 
I am using a filter on a dashboard page. I need that when I have filtered the month of July 2021, it brings me this table as a result. The values ​​of all the months of the year until July 2021:
 
YearMonthIndexIndex
Last Month
%VarIndex Last
December
%Var
2021enero401,5385,94,0%385,94,0%
2021febrero415,9401,53,6%385,97,8%
2021marzo435,9415,94,8%385,913,0%
2021abril453,7435,94,1%385,917,6%
2021mayo468,7453,73,3%385,921,5%
2021junio483,6468,73,2%385,925,3%
2021julio498,1483,63,0%385,929,1%
 
I live in a country with a very high inflation rate and it is important to measure the monthly inflation rate for each month vs. the previous month, and also vs. December of the previous year.
 
My idea is to use the same measure with the month avg. sale price to compare it with the inflation rate.
 
Could you help me? Thank you very much-
 
This is my data source:
DateIndex
01/12/2016100,0
01/01/2017101,6
01/02/2017103,7
01/03/2017106,1
01/04/2017109,0
01/05/2017110,5
01/06/2017111,8
01/07/2017113,8
01/08/2017115,4
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous 

According to your case, i think table is more suitable for you than matrix. please try it:

1.create date table as slicer

Date = SUMMARIZE('Data',[Date],"Year",YEAR([Date]),"Month",MONTH([Date]))

2.create a flag measure then apply it into filter.

flag = IF(YEAR(MAX('Data'[Date]))=MAX('Date'[Year])&&MONTH(MAX('Data'[Date]))<=MAX('Date'[Month]),1,0)

vyalanwumsft_0-1630988176792.png

3.create measures:

Index Last Month = CALCULATE(SUM('Data'[Index]),FILTER(ALL(Data),EOMONTH([Date],0)=EOMONTH(MAX('Data'[Date]),-1)))
%Va Last month = DIVIDE(SUM('Data'[Index])-[Index Last Month],[Index Last Month])
Index Last December = CALCULATE(SUM('Data'[Index]),FILTER(ALL(Data),YEAR([Date])=YEAR(MAX('Data'[Date]))-1&&[Month nbr]=12))
%Va last december = DIVIDE(SUM('Data'[Index])-[Index Last December],[Index Last December])

The final output is shown below:

vyalanwumsft_1-1630988321355.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @Anonymous 

According to your case, i think table is more suitable for you than matrix. please try it:

1.create date table as slicer

Date = SUMMARIZE('Data',[Date],"Year",YEAR([Date]),"Month",MONTH([Date]))

2.create a flag measure then apply it into filter.

flag = IF(YEAR(MAX('Data'[Date]))=MAX('Date'[Year])&&MONTH(MAX('Data'[Date]))<=MAX('Date'[Month]),1,0)

vyalanwumsft_0-1630988176792.png

3.create measures:

Index Last Month = CALCULATE(SUM('Data'[Index]),FILTER(ALL(Data),EOMONTH([Date],0)=EOMONTH(MAX('Data'[Date]),-1)))
%Va Last month = DIVIDE(SUM('Data'[Index])-[Index Last Month],[Index Last Month])
Index Last December = CALCULATE(SUM('Data'[Index]),FILTER(ALL(Data),YEAR([Date])=YEAR(MAX('Data'[Date]))-1&&[Month nbr]=12))
%Va last december = DIVIDE(SUM('Data'[Index])-[Index Last December],[Index Last December])

The final output is shown below:

vyalanwumsft_1-1630988321355.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

aj1973
Community Champion
Community Champion

Hi @Anonymous 

I tried something for you

https://drive.google.com/file/d/1BqxhEjnQMpwb4exj6lm3WY1RHh15ETiE/view?usp=sharing

aj1973_0-1630788480779.png

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Anonymous
Not applicable

Amine thank you very much for the reply.

 

Do you think you can do it in the file that I am going to give you?

 

https://drive.google.com/file/d/1yK1J8xVz_5oCh_y7GOEA6J7del_BOJj1/view?usp=sharing

 

ltroncosoba_0-1630789304883.png

 

I made something like this. Maybe you can help me with the measures.

 

I need to recreate the table, with those filters applied as they are in my example. Thank you!!!

aj1973
Community Champion
Community Champion

Hi @Anonymous 

Here you go

aj1973_0-1630845408061.png

 

https://drive.google.com/file/d/1T2A2ng-Vwfh90WBIOvCdL8xJZKGdubf5/view?usp=sharing

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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