Forum Discussion

DrewSmith's avatar
DrewSmith
Helper III
2 years ago

Variable target calculation for KPI Card

Hi, 

I'm trying to build out a new report that has a date slicer linked to a calendar table. 
I've got KPI cards showing MTD and previous MTD. 

I want to amend this so I can see, for example: 

- If I change the dates to 8th to 14th Jan the KPI card shoes those sales figures, along with 1st to 7th in the target section 
- If I select the whole of December, i'd want to see November in the target section. 
- If I select the whole of Q4 then I'd see Q3 in target section. 

How would I write a variable target calculation? 

Need Help

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi DrewSmith 

     

    Based on the question you asked, here's how to judge the slicer range:

     

    “Calendar”

     

     

    You can calculate sales by judging the slicer range dynamics

    Date Range Type = 
    VAR MinDate = MIN('Calendar'[Date])
    VAR MaxDate = MAX('Calendar'[Date])
    VAR enddate = EOMONTH(MaxDate, 0)
    VAR startdate = EOMONTH(MinDate, -1) + 1
    VAR MonthDiff = DATEDIFF(MinDate, MaxDate, MONTH)
RETURN
IF(MonthDiff = 0 && MinDate <> startdate, "Alternate Days",
    IF(MonthDiff = 0 && MaxDate = enddate && MinDate = startdate, "Whole Month",
        IF(MonthDiff = 2 && MaxDate = enddate && MinDate = startdate, "Full quarter",
            BLANK()
        )
    )
)

     

    Here is the result.

     

     

     

     

     

    Regards,

    Nono Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.