Forum Discussion
Help creating Dax Measure
- 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" ) )
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"
)
)