Forum Discussion

Sonnet's avatar
Sonnet
Icon for Helper I rankHelper I
2 years ago
Solved

KPI visual not showing correct results when using the Quarter column

Hi, I'm trying to figure how to make the 'KPI' visual display the tickets closed in the last 3 months against the the ones closed in the previous quarter (or previous 90 days).

I used this DAX measure to calculate the tickets closed :

Total Completed = CALCULATE(COUNT('Tickets'[Date Closed]), 'Tickets'[Date Closed]<>BLANK(),  USERELATIONSHIP('Calendar'[Date],'Tickets'[Date Closed]))+0
 
I use the 'Total Completed' in the Value field. Adding the Year column from the Calendar table to the trend axis, I get correct results for the Total Completed in 2024.
Adding in the trend Axis  'quarter', the result is not correct. I'd like to understand how Power bi uses the Quarter column from the Calendar table to make calculations. Does it start count 90 days back from today? Or it calculates the Total Completed from January 2024 to March 2024?
Ideally I'd like to get the number of tickets closed in the last 90 days (from 0 to -90) against the ones closed in the previous 90 days (from -91 to -180 days).
Thanks ina dvance for your help.
Need Help
  • kpost's avatar
    kpost
    2 years ago

    Quarter uses static and pre-defined 3 month chunks IE Jan-Mar, Apr-Jun etc.

    If you want dynamic calculations based on last 90 days and 180 to 90 days ago relative to today, there are several different things you might do depending on what exactly you're trying to achieve.

     

    -You could calculate them separately using measures and filtering on the [date].

    Last_90_Days = CALCULATE(COALESCE(SUM([VALUE), 0), [DATE] >= TODAY() - 90)

    180_to_90_Days_Ago = CALCULATE(COALESCE(SUM([VALUE), 0), [DATE] >= TODAY() - 180, [DATE] < TODAY() - 90)

    -An alternative would be to add a calculated column to your date table that you could use as "Quarter".  Maybe something like this....

    Custom_Quarter = CEILING(DIVIDE(TODAY() - [DATE] + 1, 90), 1)
     
    With this column, the last 90 days has value 1, the previous 90 days has value 2, etc etc.


    -Or maybe this calculated column in your date table, if you'd prefer having it show up in your visual as text.
    Custom_Quarter =
    var date_range = CEILING(DIVIDE(TODAY() - [DATE] + 1, 90), 1)
    return
        SWITCH(
            date_range ,
            1, "Last 90 days",
            2, "180 to 91 days ago",
            3, "270 to 181 days ago",
            "Long time ago"
        )


     
    Totally depends on what, exactly, you're trying to do here.  Hopefully one of these ideas proves useful to you.



    ///Mediocre Power BI advice, but it's free///

1 Reply

  • kpost's avatar
    kpost
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Quarter uses static and pre-defined 3 month chunks IE Jan-Mar, Apr-Jun etc.

    If you want dynamic calculations based on last 90 days and 180 to 90 days ago relative to today, there are several different things you might do depending on what exactly you're trying to achieve.

     

    -You could calculate them separately using measures and filtering on the [date].

    Last_90_Days = CALCULATE(COALESCE(SUM([VALUE), 0), [DATE] >= TODAY() - 90)

    180_to_90_Days_Ago = CALCULATE(COALESCE(SUM([VALUE), 0), [DATE] >= TODAY() - 180, [DATE] < TODAY() - 90)

    -An alternative would be to add a calculated column to your date table that you could use as "Quarter".  Maybe something like this....

    Custom_Quarter = CEILING(DIVIDE(TODAY() - [DATE] + 1, 90), 1)
     
    With this column, the last 90 days has value 1, the previous 90 days has value 2, etc etc.


    -Or maybe this calculated column in your date table, if you'd prefer having it show up in your visual as text.
    Custom_Quarter =
    var date_range = CEILING(DIVIDE(TODAY() - [DATE] + 1, 90), 1)
    return
        SWITCH(
            date_range ,
            1, "Last 90 days",
            2, "180 to 91 days ago",
            3, "270 to 181 days ago",
            "Long time ago"
        )


     
    Totally depends on what, exactly, you're trying to do here.  Hopefully one of these ideas proves useful to you.



    ///Mediocre Power BI advice, but it's free///