Forum Discussion
Helper IIMonth on Month Change with simple & calculated columns
Hi Everyone
I have an issue that I'm sure many others have posted (I've tried the suggestions - none of them I can get to work) and would like some guidance based on my data table and columns
Challenge - show the variance in % between months for positive reviews
Columns available - Date (ddmmmmyyyy), Month, Quarter, Year, YearMonth (yyyymm) and Sentiment
Note: my Sentiment column contains text values of POSITIVE, NEGATIVE, MIXED and NEUTRAL but I also have a calculated field which already counts the number of POSITIVE instances
I've only just started using Power BI today (20/11/2019 if the date doesn't show) so I'm struggling a fair bit with more complicated things
Any advice would be really helpful
Thanks
Dan_B
First of all create a power bi calendar
refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
if need use format command to make a date in your format to create a join with your table.
You can get this month vs last month like this. Here 'Date' is my date dimension and [Date Filer] is the date column in that. It joined with sales on order_date
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer])) Last month Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
2 Replies
- amitchandak
Super User
First of all create a power bi calendar
refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
if need use format command to make a date in your format to create a join with your table.
You can get this month vs last month like this. Here 'Date' is my date dimension and [Date Filer] is the date column in that. It joined with sales on order_date
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer])) Last month Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
Daniel_BThanks amitchandak - the measures are working, I just need to visualise them now
Any tips on a good visualisation to use or links to good sites for visualisation?
Many thanks again
Dan_B