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 measu...
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///

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