Forum Discussion
Comparing latest data with second latest uploaded data and comparing data sets
Hi Guys!
I am working on a dashboard in which I upload monthly financial reports. I have connected my dashboards to folders, so the data stacks up. I want to build something with which I can compare the latest report with the previous one and this has to automatically flow whenever I upload multiple reports over time.
So, basically I want to make a matrix table that show's me the most recent report compared next to the previous report in which I can calculate the differences between them. The dates that reports get uploaded do not have a specific frequence. Sometimes it's 3 times a month, sometimes once every two months. The data I use look somewhat like below
| Date Created | Source name | Amount | category |
| 14-4-2021 | 0414 - report | € 1.500,00 | Available |
| 14-4-2021 | 0414 - report | € 2.500,00 | Budget |
| 14-4-2021 | 0414 - report | € 1.750,00 | Spend |
| 14-4-2021 | 0414 - report | € 1.500,00 | Plan 0 |
| 22-4-2021 | 0422 - report | € 2.000,00 | Available |
| 22-4-2021 | 0422 - report | € 3.000,00 | Budget |
| 22-4-2021 | 0422 - report | € 1.750,00 | Spend |
| 22-4-2021 | 0422 - report | € 1.500,00 | Plan 0 |
| 2-5-2021 | 0205 - report | € 2.000,00 | Available |
| 2-5-2021 | 0205 - report | € 2.500,00 | Budget |
| 2-5-2021 | 0205 - report | € 2.500,00 | Spend |
| 2-5-2021 | 0205 - report | € 1.000,00 | Plan 0 |
And this is the output i would like to see, only the last 2 reports with the difference between them.
| 0422 - report | 0205 - report | Difference | |
| available | € 2.000,00 | € 2.000,00 | € - |
| Budget | € 3.000,00 | € 2.500,00 | € -500,00 |
| Sped | € 1.750,00 | € 2.500,00 | € 750,00 |
| Plan 0 | € 1.500,00 | € 1.000,00 | € -500,00 |
What would you guys recommend?
Thanks!
Hi Anonymous ,
Please try to create a calculated table to contain the latest two report:
TableTop2 = VAR A = TOPN(2,VALUES('Table'[Date Created]),'Table'[Date Created],DESC) RETURN FILTER('Table','Table'[Date Created] IN A)Then create the following measure:
Difference = IF(ISFILTERED(TableTop2[Source name]),SUM(TableTop2[ Amount ]),CALCULATE(SUM(TableTop2[ Amount ]),FILTER('TableTop2',TableTop2[Date Created] = MAX(TableTop2[Date Created])))- CALCULATE(SUM(TableTop2[ Amount ]),FILTER('TableTop2',TableTop2[Date Created] = MIN(TableTop2[Date Created]))))Then create a matrix based on the new calculated table(it will update automatically after you refresh):
You can also refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
10 Replies
Anonymous , Create a meausre like
measure =
var _max = maxx(allselected(Table), Table[Date Created])
var _Min= maxx(filter(Table, Table[Date Created] <_max), Table[Date Created)return
sumx(filter(allselected(Table,Table[category]) =max(Table[category]) && Table[Date Created] =_max ),Table[Amount]) -sumx(filter(allselected(Table,Table[category]) =max(Table[category]) && Table[Date Created] =_max ),Table[Amount])if needed add this in filter too
&& Table[report] = max(table[Report])
Hi, Anonymous
Please check the below picture and the sample pbix file's link down below.
All measures are in the sample pbix file.
https://www.dropbox.com/s/d1k3gzujhd6jevh/wilm117.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
Hi Jihwan_Kim,
You solutions comes close. It works for the 'latest report', but not for the '2nd latest report'. The real data sets is a bit more complex and I was hoping that the simplified version was sufficient to come to a solution. This is a screenshot from the actual data
I basically want the measures that show me all the data from the latest date created (22-04-2021) and the 2nd latest date created (14-04-2021) and that this automatically adjusts when I upload a new batch of data. With the separate measures i made for the column [post] i can specify these values per [post]. Furthermore i Want to show my data on row-level per WBS element (column 'Niv. 0.1). It would be somehting like this:
14-04-2021 22-04-2021 Difference Niv. 0.1 budget Budget - wbs1 - wbs2 - wbs3 - Hi, Anonymous
Thank you for your feedback.
I think just one more condition is needed in each measure, which is limiting the calculation only within the same Niv. 0.1.
I have one question, and that is, what is the difference between the first row and the second row? What I can see is that only the Jaar Date column's value is different. Do you want to sum those two rows?
If it is OK with you, please share your sample pbix file's link here then I can try to come up with a more accurate measure.
Thank you.
Hi Anonymous ,
Please try to create a calculated table to contain the latest two report:
TableTop2 = VAR A = TOPN(2,VALUES('Table'[Date Created]),'Table'[Date Created],DESC) RETURN FILTER('Table','Table'[Date Created] IN A)Then create the following measure:
Difference = IF(ISFILTERED(TableTop2[Source name]),SUM(TableTop2[ Amount ]),CALCULATE(SUM(TableTop2[ Amount ]),FILTER('TableTop2',TableTop2[Date Created] = MAX(TableTop2[Date Created])))- CALCULATE(SUM(TableTop2[ Amount ]),FILTER('TableTop2',TableTop2[Date Created] = MIN(TableTop2[Date Created]))))Then create a matrix based on the new calculated table(it will update automatically after you refresh):
You can also refer to the pbix file.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
