Dynamic month report with versus prior month

[thomsontam]

Hi,

I could like to get a report like the following layout 

Customer	Dec-20	Jan-21	Feb-21	VPM
A	12	10	9	-1
B	10	11	12	1
C	8	9	9	0

I am able to use a dynamic month measure to get individual month and VPM calculate but I only managed to present using the following layout :

	Dec-20		Jan-21		Feb-21
Customer	Sales	VPM	Sales	VPM	Sales	VPM
A	12		10	-2	9	-1
B	10		11	1	12	1
C	8		9	1	9	0

May I ask are there anyway I can write a DAX that can shows a report like the first layout?

Thanks!

[Jihwan_Kim · Accepted solution]

Hi, @thomsontam 

Please check the below picture and the sample pbix file's link down below.

I am not sure about how your whole data model looks like. But I want to suggest having an additional table for the new column that contains VPM. The measures are in the sample pbix file.

If it is OK with you, please share your sample pbix file, then I can try to come up with more relevant measures suggestions.

https://www.dropbox.com/s/94nca2lwpklbxfz/Omar.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

[thomsontam]

Hi Kim,

Thanks for your reply, your output looks as same as what I am looking for, however, could you please reshare the file in DropBox? The file in dropbox looks difference then the picture shows above.

Thanks again!

Regards,

Thomson

[Jihwan_Kim]

Hi, @thomsontam 

Sorry, my mistake.

Please check the below link.

https://www.dropbox.com/s/9j1ycogucaeootu/thomson.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

[thomsontam]

My data structure is very similar to yours, so I can adjust my table for it.

Only 1 thing I still try to do myself is the date range selection working on earlier period to last month. (any time up to April).  However, if I select From Dec 20 to Mar 21 then VPM not give the correct result.  
edit: I modify the DAX so I can get any 4 months period with the last one with VPM based on a Period selector.  Thanks for your help KIM, it really helps on my dashboard report!

Thanks for your help, I can use this method for my other monthly trend report which need the % of Grand total at the end! 

[amitchandak]

@thomsontam , seem like you need a hybrid table

if you are looking for a Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc