Forum Discussion

sufiyanakhtar's avatar
sufiyanakhtar
Helper I
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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    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
4 years ago

Sure Sparta, but please do let me know for me to understand this better 🙂

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    sufiyanakhtar so as I said, I have a suspect, but first need to do some testing to verify my guess.
    Run this measure and tell me if you still get the right result or the same as the original wrong result:

     

    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
                ),
                '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
                ),
                '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
                ),
                'CORPDW ACCOUNT'[AccType] = "Expense"
            )
    )

     


    Also, don't forget to mark the message with the measure that worked as a solution for community visabilty



    • sufiyanakhtar's avatar
      sufiyanakhtar
      Helper I
      4 years ago

      Hey Sparta,

      Both of them yield the same result which is shocking tbh because I tried the same formula as your most recent one and it didnt work before. Anyways, both of them yield the same output

      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        sufiyanakhtar it is strange.
        So you are saying that both worked but this (your original) didn't:

         

        Measure1 =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Period Slicers'[Period Slicer] ) = "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"
        ),
    BLANK ()
)

         

        Can you please verify this again?