Forum Discussion
KPI Card Month-over-Month Change (Current & Previous Month)
I am struggling to create a KPI Card showing this month's number of Cars with the amount/percent change. I started with a table and will convert to KPI Card once my measures work. I started by creating a measure for Total Cars:
Total Cars = SUM('CarsTable'[Cars])
Then, I wanted to create a Month Over Month (MoM) measure. I thought what I have below would work, but even though it accepts the query, it produces blank value output for MoM when I add it as a column in the table.
MoM =
CALCULATE(
[Total Cars]
, PREVIOUSMONTH( 'CarsTable'[Date] )
)
I then tried creating a Calendar table and created a relationship between the Date in the Calendar table and the Date in the CarsTable. When doing so, the MoM values populated and I was able to create the KPI Card, but it removed my Date Hierarchy in the CarsTable. I need to keep the Date Hierarchy in the CarsTable for my other reports because now they have Dates listed instead of Months.
I thought maybe modifying my MoM measure adding the hierarchy Month field would work, but it does not accept this query:
MoM =
CALCULATE(
[Total Cars]
, PREVIOUSMONTH( 'CarsTable'[Date].[Month] )
)
Is there a way for me to create a KPI Card showing the month-over-month change while keeping my Date Hierarchy in the CarsTable so my other reports aren't affected?
Thanks,
Weez
In order use DAX Time intelligence functions, you have to have that calendar table with individual dates. One thing that you can do for your other visuals is add a column for month to the calendar. This will make all dates have the coresponding month.
If you used the CALENDAR('startdate',enddate') function to generate your calendar, the easiest solution is to add a calculated column with Month Name = FORMAT('Table'[Date], "MMMM")
This will give you a result like this:
Then on your other visuals you can use that field. If you have more than a tears worth of data there is another step that you will have to do to differentiate between the years.
Please let me know if this helps.
3 Replies
In order use DAX Time intelligence functions, you have to have that calendar table with individual dates. One thing that you can do for your other visuals is add a column for month to the calendar. This will make all dates have the coresponding month.
If you used the CALENDAR('startdate',enddate') function to generate your calendar, the easiest solution is to add a calculated column with Month Name = FORMAT('Table'[Date], "MMMM")
This will give you a result like this:
Then on your other visuals you can use that field. If you have more than a tears worth of data there is another step that you will have to do to differentiate between the years.
Please let me know if this helps.This is very helpful, thank you! The only thing I'm struggling with now is creating a relationship between the Date in the Calendar table and my 2 other tables (CarsTable & TrucksTable.) It's telling me "An active relationship already exists between CarsTable and TrucksTable. To make this relationship active, deactivate the existing relationship first." I have those two tables linked by the DealerID field.
How do I get the Calendar table linked to both of my other two tables?
Never mind! I created a 2nd Calendar table to use for my TrucksTable data and joined those two tables. I finally got it. Thank you so much for your help
