Forum Discussion

mdjoshua94's avatar
mdjoshua94
Frequent Visitor
6 years ago

KPI - Compare column data by month

MonthIssue
JanResolved
JanOpen
JanOpen
FebResolved
FebResolved
FebResolved
FebResolved

 

Hi all, I am keen to show a card that will show as "100% Resolved with a Thumbs Up" whereby The Thumbs up is a condition that compares against data from Feb & Jan. I had used the below (but it requires me to manually change the values and change filters...

Ratio = [Count of Closed on Time for Closed On Time]/[Count of Closed on Time total for Closed on Time]
KPI RATIO = [Ratio]*100 &"%" &
IF(
[Ratio] <= 0.5 ,
-- THEN --
UNICHAR(128402) , -- Thumbs up!
-- ELSE --
UNICHAR(128403) -- Thumbs down
)




Need Help

3 Replies

    • mdjoshua94's avatar
      mdjoshua94
      Frequent Visitor
      6 years ago
       
       
       



      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. 

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        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/