Forum Discussion
Current Month Vs Last Month Bar Graph
Hi,
I have a bar graph that successfully shows current month vs last month data by product.
Problem I have is there is 1 product where current month value is 0 but i know contains last month, and this is not showing in the graph at all.
Example
Current Month Previous Month
Product A 1 2
Product B 2 3
Product C 0 5 <<--- This is not showing on the graph.
Setup is I have a month page filter and a measure to extract prevous month data.
Thanks
Sion
Hi siontk ,
To use time intelligence function, you'd better have a separate date table to use.
Or you can create measure like this if you just have a single table without using time intelligence function:
Current month = CALCULATE( SUM('Table'[Sales]), MONTH('Table'[date]) = MONTH(TODAY()) ) Previous month = CALCULATE( SUM('Table'[Sales]), MONTH('Table'[date]) = MONTH(TODAY()) -1 )Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
siontk , hope you are using date table marked as date,
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))Power BI — MTD Questions — Time Intelligence 3–5
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090eTo get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.Hi,
I don't have a Date Table (all dates and data in a single table). Could I still use the DatesMTD regardless?
Thanks
Sion
v-yingjlCommunity Support
Hi siontk ,
To use time intelligence function, you'd better have a separate date table to use.
Or you can create measure like this if you just have a single table without using time intelligence function:
Current month = CALCULATE( SUM('Table'[Sales]), MONTH('Table'[date]) = MONTH(TODAY()) ) Previous month = CALCULATE( SUM('Table'[Sales]), MONTH('Table'[date]) = MONTH(TODAY()) -1 )Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- Ashish_Mathur
Hi,
If you have a Date Table, then the following meaures will work
Total sales = SUM(Data[Sales])
Total sales in last month = calculate([total salees],previousmonth(calendar[date]))
Hope this helps.
