Forum Discussion
KPI - Compare column data by month
mdjoshua94 , which measure you are not able to calculate here ??
amitchandak , I am unable to measure based on months. My current measure uses a fixed value. I intend to compare the current month and the previous month.
mdjoshua94 , if you have dates you can use time intelligence and date table to get this month vs last month
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))Another way is to use rank. But for that, you need Asc rank on month
This month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[month Rank]=max('Date'[month Rank])))
Last month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[month Rank]=max('Date'[month Rank])-1))It can be a date or month table . prefer a separate table for time
To 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/