Forum Discussion

sufiyanakhtar's avatar
4 years ago
Solved

Help creating Dax Measure

Dear Power Users,

I have a model as shown below. I am also having a disconnected table which is acting as slicers for my value (MTD,QTD,YTD). I want to calculate a measure based on the slicers. I am currently using this formula:

 

Measure = 

SWITCH(TRUE(),
SELECTEDVALUE(Slicer Value) = "MTD", CALCULATE(SUM('CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency]), DATESBETWEEN('CORPDW FISCALYEARROLLINGPERIOD'[Day], MIN('CORPDW FISCALYEARROLLINGPERIOD'[MTD_START_DATE]), MAX('CORPDW FISCALYEARROLLINGPERIOD'[MTD_END_DATE])) ,'CORPDW ACCOUNT'[AccType] = "Expense"),

SELECTEDVALUE('Period Slicers'[Period Slicer]) = "QTD", CALCULATE(SUM('CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency]), DATESBETWEEN('CORPDW FISCALYEARROLLINGPERIOD'[Day], MIN('CORPDW FISCALYEARROLLINGPERIOD'[QTD_START_DATE]), MAX('CORPDW FISCALYEARROLLINGPERIOD'[QTD_END_DATE])) ,'CORPDW ACCOUNT'[AccType] = "Expense"),

SELECTEDVALUE('Period Slicers'[Period Slicer]) = "YTD", CALCULATE(SUM('CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency]), DATESBETWEEN('CORPDW FISCALYEARROLLINGPERIOD'[Day], MIN('CORPDW FISCALYEARROLLINGPERIOD'[YTD_START_DATE]), MAX('CORPDW FISCALYEARROLLINGPERIOD'[YTD_END_DATE])) ,'CORPDW ACCOUNT'[AccType] = "Expense"),


""
)

 

 

The issue is that no matter I select MTD,QTD OR YTD, it shows me the same value. Please help!!!

  • sufiyanakhtar hey, you implemented the logic not in the classic way so hard to say what is going on.
    You should go read and be familiar with the best practice tecniques for these time intelligence patterns:
    https://www.daxpatterns.com/time-patterns/

    Another important question: I see you used the column  'CORPDW FISCALYEARROLLINGPERIOD'[Day]
    in your measures. Is that a data or a day number between 1 to 31?
    It needs to be a date, maybe that is all the issue.
    I anyway cleaned up your code a little and also added REMOVEFILTERS just to be sure but in case it's a 
    'CORPDW FISCALYEARROLLINGPERIOD'[Day] date column than it's not neccesary. So, this is the most important question, what is that column.

     

    Measure1 =
    VAR _slicr_value =
        SELECTEDVALUE ( 'Period Slicers'[Period Slicer] )
    RETURN
        SWITCH (
            _slicr_value,
            "MTD",
            VAR _mtd_start_date = MIN ( 'CORPDW FISCALYEARROLLINGPERIOD'[MTD_START_DATE] )
            VAR _mtd_end_date = MAX ( 'CORPDW FISCALYEARROLLINGPERIOD'[MTD_END_DATE] )
            RETURN
                CALCULATE (
                    SUM ( 'CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency] ),
                    DATESBETWEEN (
                        'CORPDW FISCALYEARROLLINGPERIOD'[Day],
                        _mtd_start_date,
                        _mtd_end_date
                    ),
                    REMOVEFILTERS('CORPDW FISCALYEARROLLINGPERIOD'),
                    'CORPDW ACCOUNT'[AccType] = "Expense"
                ),
            "QTD",
            VAR _qtd_start_date = MIN ( 'CORPDW FISCALYEARROLLINGPERIOD'[QTD_START_DATE] )
            VAR _qtd_end_date = MAX ( 'CORPDW FISCALYEARROLLINGPERIOD'[QTD_END_DATE] )
            RETURN    
                CALCULATE (
                    SUM ( 'CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency] ),
                    DATESBETWEEN (
                        'CORPDW FISCALYEARROLLINGPERIOD'[Day],
                        _qtd_start_date,
                        _qtd_end_date
                    ),
                    REMOVEFILTERS('CORPDW FISCALYEARROLLINGPERIOD'),
                    'CORPDW ACCOUNT'[AccType] = "Expense"
                ),
            "YTD",
            VAR _ytd_start_date = MIN ( 'CORPDW FISCALYEARROLLINGPERIOD'[YTD_START_DATE] )
            VAR _ytd_end_date = MAX ( 'CORPDW FISCALYEARROLLINGPERIOD'[YTD_END_DATE] )
            RETURN
                CALCULATE (
                    SUM ( 'CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency] ),
                    DATESBETWEEN (
                        'CORPDW FISCALYEARROLLINGPERIOD'[Day],
                        _ytd_start_date,
                        _ytd_end_date
                    ),
                    REMOVEFILTERS('CORPDW FISCALYEARROLLINGPERIOD'),
                    'CORPDW ACCOUNT'[AccType] = "Expense"
                )
        )

     

     


    Showcase Report – Contoso By SpartaBI


          

11 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion

    sufiyanakhtar hey, you implemented the logic not in the classic way so hard to say what is going on.
    You should go read and be familiar with the best practice tecniques for these time intelligence patterns:
    https://www.daxpatterns.com/time-patterns/

    Another important question: I see you used the column  'CORPDW FISCALYEARROLLINGPERIOD'[Day]
    in your measures. Is that a data or a day number between 1 to 31?
    It needs to be a date, maybe that is all the issue.
    I anyway cleaned up your code a little and also added REMOVEFILTERS just to be sure but in case it's a 
    'CORPDW FISCALYEARROLLINGPERIOD'[Day] date column than it's not neccesary. So, this is the most important question, what is that column.

     

    Measure1 =
    VAR _slicr_value =
        SELECTEDVALUE ( 'Period Slicers'[Period Slicer] )
    RETURN
        SWITCH (
            _slicr_value,
            "MTD",
            VAR _mtd_start_date = MIN ( 'CORPDW FISCALYEARROLLINGPERIOD'[MTD_START_DATE] )
            VAR _mtd_end_date = MAX ( 'CORPDW FISCALYEARROLLINGPERIOD'[MTD_END_DATE] )
            RETURN
                CALCULATE (
                    SUM ( 'CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency] ),
                    DATESBETWEEN (
                        'CORPDW FISCALYEARROLLINGPERIOD'[Day],
                        _mtd_start_date,
                        _mtd_end_date
                    ),
                    REMOVEFILTERS('CORPDW FISCALYEARROLLINGPERIOD'),
                    'CORPDW ACCOUNT'[AccType] = "Expense"
                ),
            "QTD",
            VAR _qtd_start_date = MIN ( 'CORPDW FISCALYEARROLLINGPERIOD'[QTD_START_DATE] )
            VAR _qtd_end_date = MAX ( 'CORPDW FISCALYEARROLLINGPERIOD'[QTD_END_DATE] )
            RETURN    
                CALCULATE (
                    SUM ( 'CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency] ),
                    DATESBETWEEN (
                        'CORPDW FISCALYEARROLLINGPERIOD'[Day],
                        _qtd_start_date,
                        _qtd_end_date
                    ),
                    REMOVEFILTERS('CORPDW FISCALYEARROLLINGPERIOD'),
                    'CORPDW ACCOUNT'[AccType] = "Expense"
                ),
            "YTD",
            VAR _ytd_start_date = MIN ( 'CORPDW FISCALYEARROLLINGPERIOD'[YTD_START_DATE] )
            VAR _ytd_end_date = MAX ( 'CORPDW FISCALYEARROLLINGPERIOD'[YTD_END_DATE] )
            RETURN
                CALCULATE (
                    SUM ( 'CORPDW TRIALBALANCE'[Amount_in_Transaction_Currency] ),
                    DATESBETWEEN (
                        'CORPDW FISCALYEARROLLINGPERIOD'[Day],
                        _ytd_start_date,
                        _ytd_end_date
                    ),
                    REMOVEFILTERS('CORPDW FISCALYEARROLLINGPERIOD'),
                    'CORPDW ACCOUNT'[AccType] = "Expense"
                )
        )

     

     


    Showcase Report – Contoso By SpartaBI


          

    • sufiyanakhtar's avatar
      sufiyanakhtar
      Helper I

      Hey Sparta, The day column is a date type column. Actually my Data architect has designed the backend because the data is aggregated on monthly basis. So the day you are referring to has dates like 01/01/2020, 02/01/2020, 03/01/2020 (MM/DD/YYYY) and so on.... Please help as IVE BEEN BREAKING MY HEAD ON THIS 💔

      • SpartaBI's avatar
        SpartaBI
        Community Champion

        sufiyanakhtar 
        Wait, you have a proper data table? Continous and unique dates or the data table is on a monthly granularity?
        P.S.  Did you check my measure anyway?