Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Year | Month | Index | Index Last Month | %Var | Index Last December | %Var |
2021 | enero | 401,5 | 385,9 | 4,0% | 385,9 | 4,0% |
2021 | febrero | 415,9 | 401,5 | 3,6% | 385,9 | 7,8% |
2021 | marzo | 435,9 | 415,9 | 4,8% | 385,9 | 13,0% |
2021 | abril | 453,7 | 435,9 | 4,1% | 385,9 | 17,6% |
2021 | mayo | 468,7 | 453,7 | 3,3% | 385,9 | 21,5% |
2021 | junio | 483,6 | 468,7 | 3,2% | 385,9 | 25,3% |
2021 | julio | 498,1 | 483,6 | 3,0% | 385,9 | 29,1% |
Date | Index |
01/12/2016 | 100,0 |
01/01/2017 | 101,6 |
01/02/2017 | 103,7 |
01/03/2017 | 106,1 |
01/04/2017 | 109,0 |
01/05/2017 | 110,5 |
01/06/2017 | 111,8 |
01/07/2017 | 113,8 |
01/08/2017 | 115,4 |
Solved! Go to Solution.
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)
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:
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.
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)
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:
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.
Hi @Anonymous
I tried something for you
https://drive.google.com/file/d/1BqxhEjnQMpwb4exj6lm3WY1RHh15ETiE/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
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
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!!!
Hi @Anonymous
Here you go
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
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |