Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Comparing Month Year Across Different Years in the KPI Visual

Hello all! 

 

I am having trouble comparing different years in the KPI visual. When I select Month and Years that are in the same year, the KPI visual shows the current information as shown in this screenshot. 

 

1jxYxUe.png

 

 

However, when comparing Month Years that go across different years (2019 vs. 2020), the KPI visual only shows data for 2020. 

 

C9jpQDM.png

 

I have the trend axis set to Year. Is there a way for me to add something like a "Century" column in my Date table which would allow me to compare 2019 and 2020 data in the same KPI visual? Currently, it only shows data for the most recent year (2020). 

 

 

Current KPI setup: 

gpa0RU5.png

 

Alternatively, are there any custom KPI visuals that allow me to skip the trend axis and just compare the raw numbers of Total Form Fills & Previous Period Form Fills? I am not interested in the trend visual in the KPI, just the comparison of the 2 numbers and the % difference of these numbers. I also like how the number is green if the Form Fills are greater than the Previous Period Form Fills and red if it is lower than the Previous Period Form Fills. 

 

Thanks in advance for your suggestions! 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

amitchandakk , I have decided to create a "Start of Century Column in my date table by using a SWITCH function. 

 

 

 

Start of Century = SWITCH(
    Date_tbl[Year],
    2016,2000,
    2017,2000,
    2018,2000,
    2019,2000,
    2020,2000,
    2021,2000,
    2022,2000,
    2023,2000,
    2024,2000,
    2025,2000,
    0
)

 

 

 This allows me to compare my month years across different years. 

 

Like this: eN1xUNh.png

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , if these are continuous month you should be able to do with time intelligence and date table

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))))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

 

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/

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.

Anonymous
Not applicable

amitchandakk , I have decided to create a "Start of Century Column in my date table by using a SWITCH function. 

 

 

 

Start of Century = SWITCH(
    Date_tbl[Year],
    2016,2000,
    2017,2000,
    2018,2000,
    2019,2000,
    2020,2000,
    2021,2000,
    2022,2000,
    2023,2000,
    2024,2000,
    2025,2000,
    0
)

 

 

 This allows me to compare my month years across different years. 

 

Like this: eN1xUNh.png

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.