DAX measure - based on the current selected quarter slicer

DAX DAX Calculations
1

Hi everyone, having a hard time figuring out how to have a dynamic dax based on selectedvalue of the slicer (quarter).

Here’s my dax measure. What it does is that it tracks the target revenue every quarter if it has been reached or not. So, if the target is reached, it will execute the VAR _TargetReach else _TargetNotReach.
How can I make it a dynamic base on the selectedvalue of a slicer quarter ? Say with current selected quarter Q2, and in _PreviousQuarter, I have not reached the target, what is supposed to do is that it invokes that VAR _TargetNotReach else _VAR _TargetReach. Thanks in advance :slight_smile:

Bonus Policy Quarter (Revenue) = 
VAR _TargetReach = [Bonus Policy (Revenue)]
VAR _TargetNotReach = [Bonus Policy (Trigger Point)] - [Bonus Policy (Revenue)]
VAR _PreviousQuarter =
    SELECTEDVALUE ( Dates[Quarter Of Year] )-1
VAR _CurrentQuarter = 
    SELECTEDVALUE(Dates[Quarter Of Year])
RETURN
    IF (
        [Bonus Policy (Revenue)] < [Bonus Policy (Trigger Point)],
        _TargetNotReach,
        _TargetReach
    )
2

Good evening @ronald_balza ,

It is a bit hard to offer a solution, which meets your requirements without a sample PBIX-model.
Questions :

  1. What context do you want in your envisioned report ?

  2. Why do you need previous quarter ?

  3. If you were looking for previous quarter sales, it seems sufficient to use a base measure, like

    Prev.Quarter Sales = CALCULATE([Actual Sales], DATEADD(DateT[Date],-1,QUARTER))

  4. Which targets have you defined, are those specific per quarter and per salesperson ?

  5. What is _CurrentQuarter, is it sales, could it be replaced by SUM(Sales[Sales]) and using the context of the current quarter, like Q1 2022 ?

Not sure if it meets your information requirements, but a workout of a possible solution.
It requires in the context Sales Person from the sales table for meaningful information in this set up, to get the correct YES/NO answer if the target is reached ! For a correct answer per quarter, also the Quarters/ “QuarterlyInCalendar” context is needed.

See attached PBIX:
Sales Target reached example.pbix (93.2 KB)

  1. Data model including two target methodologies per sales person: a specific target per quarter and a generic target
    image

    Note: presumably Generic Quarterly Target will not be used, as it will be a target over years, as the table “Generic Table” does not contain any dates

  2. Measures set up for “Quarter Specific Target” and “Specific Target reached”

    Quarter specific Target = calculate( SUM( 'Target per quarter'[Quarter specific  Target]), 
                                    FILTER('Target per quarter','Target per quarter'[SalesPerson] = SELECTEDVALUE(Sales[SalesPerson])))

    Specific Target reached = if(Not(isblank([Actual Sales])), 
                                     IF([Actual Sales] > [Quarter specific Target] , "Yes", "No"),
                            BLANK())

  3. An initial report presented, with Specific Target Reached, and Generic Target reached

    image
    image827×241 16.2 KB

Please let me know your comments,

Kind regards, Jan van der Wind

1 Like