Variable target calculation for KPI Card

[DrewSmith]

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? 

[Anonymous]

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.