Forum Discussion
Comparison- current month vs previous month
excel filepower bi datamatrix
I want to create a comparison matrix. Using the current month revenue minus previous month revenue. Is there anyway to do this?
Sure there is a way! :-)
You can use DAX to creat the appropriate measures to show in your matrix. See some example here: https://powerbi.tips/2016/07/measures-month-to-month-percent-change/.
But first you need to make sure the Date column is of Date data type - you need this in order to use Time Intelligence functions in DAX and to allow Power BI to deal with time hierarchy.
Hope that helps.
Cheers,
Pawel
OK, try out this .pbix file and see if the approach used inside fits your need: http://blog.sqlgeek.pl/Download/DAX - Month over Month.pbix.
I used the same DAX function mentioned in the article (PREVIOUSMONTH). And presence of the regions in your data doesn't change much.
Cheers,
Pawel
12 Replies
Sure there is a way! :-)
You can use DAX to creat the appropriate measures to show in your matrix. See some example here: https://powerbi.tips/2016/07/measures-month-to-month-percent-change/.
But first you need to make sure the Date column is of Date data type - you need this in order to use Time Intelligence functions in DAX and to allow Power BI to deal with time hierarchy.
Hope that helps.
Cheers,
Pawel
thx for the suggestion...but it doesn't work on my data...as u can see i have repeated region in every month...it is the difference between my data n your data
OK, try out this .pbix file and see if the approach used inside fits your need: http://blog.sqlgeek.pl/Download/DAX - Month over Month.pbix.
I used the same DAX function mentioned in the article (PREVIOUSMONTH). And presence of the regions in your data doesn't change much.
Cheers,
Pawel
Anonymous hi, i have a another question. I have more 10tables like this. However, I tried to create same measures in every single table. However, it doesn't allow me to use the same name of the measure (i have to use the same name in order for presentation). Is it possible to create only one measures in one table only and it will work for every tables?
Thanks.
Anonymous basically what i'm trying to say is there any other ways that can i copy and paste all of the existing measure into another table with the same name but slightly different formula.
thanks.
Hello Anonymous
Thank you for your contribution to this topic.
I am looking for same type of comparison, but just with current weeknum vs. previous weeknum.
I have tried Google, and there does not seem to be a PREVIOUSWEEK function like there is a PREVIOUSMONTH function you refer to.How would you go about comparing week numbers?
Best regards
DavidI've found that creating a date table with every required breakdown of the date (ie: Month number, Week number) is a good practice. This numbering should just be a sequential number from the begining of your date range of the date table to the most recent date. That way you can use simple DAX like: Lead PM = CALCULATE([Leads], FILTER(ALL(CreateDateTable), CreateDateTable[MonthNumber]= MAX(CreateDateTable[MonthNumber])-1). If the MonthNumber is a running number from 1 to 36 for a 3 yr period, then you don't won't have an issue considering the year. Same can apply to Week number.
This article was helpful: http://www.daxpatterns.com/time-patterns/
Here are some tutorials on generating a date calendar with an Index for months, Years, Days, and weeks. This uses the same logic as steph_io Great solution.
https://powerbi.tips/2017/11/creating-a-dax-calendar/
Hi,
I tried the same with data set i have, and its not working. Could you help me out here if possible?? Have attached the link to PBIX Download
Thanks
