Forum Discussion

sure19's avatar
sure19
Icon for Helper II rankHelper II
6 years ago
Solved

SAMEPERIOD Lastyear

I need to calculate sameperiod last year not with the calander year, but with the data that is available.  Because i get the current data one month late, so when i calculate the dax formula (sameperi...
DAX
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi sure19 

     

    you can try something like this:

    var _maxDate = CALCULATE(max('Table'[Date]),ALL('Table'))
return
date(YEAR(_maxDate),MONTH(_maxDate)-12,1)

     

    where Table[Date] should be the date column in your data table.

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too. 

  • sure19's avatar
    sure19
    6 years ago

    Thanks sturlaws  for this solution, it worked.

  • sturlaws's avatar
    sturlaws
    6 years ago

    You should probably use the filter-function, and you are also missing the criteria for the date. Try something like this:

    MMBFQUAYYTD19 =
VAR _MXDATE =
    CALCULATE ( MAX ( TP_ENG_METRICS[MONTHDATE] ), ALL ( 'TP_ENG_METRICS' ) )
RETURN
    CALCULATE (
        AVERAGE ( TP_ENG_KPI[KPI_VALUE] ),
        FILTER (
            'TP_ENG_KPI',
            'TP_ENG_KPI'[KPI] IN { "MMBF FUN" }
                && TP_ENG_KPI[MonthDate]
                    >= DATE ( YEAR ( _MXDATE ), MONTH ( _MXDATE ) - 12, 1 )
        )
    )

     

sure19's avatar
sure19
Icon for Helper II rankHelper II
6 years ago

Hi sturlaws  when i use this in my formula, i get some error.  it works independently as a measure though.  Here below it gives some error, 

 

MMBFQUAYYTD19 =
VAR _MXDATE =
CALCULATE ( MAX ( TP_ENG_METRICS[MONTHDATE] ), ALL ( 'TP_ENG_METRICS' ) )
RETURN
CALCULATE(AVERAGE(TP_ENG_KPI[KPI_VALUE]),
'TP_ENG_KPI'[KPI] IN { "MMBF FUN" },DATE ( YEAR ( _MXDATE ), MONTH ( _MXDATE ) - 12, 1 ))
 
 

the error is "The True/False expression does not specify a column.  Each True/False expressions used as a table filter expression must refer to exactly one column.

 

 

 

sturlaws's avatar
sturlaws
Icon for Resident Rockstar rankResident Rockstar
6 years ago

You should probably use the filter-function, and you are also missing the criteria for the date. Try something like this:

MMBFQUAYYTD19 =
VAR _MXDATE =
    CALCULATE ( MAX ( TP_ENG_METRICS[MONTHDATE] ), ALL ( 'TP_ENG_METRICS' ) )
RETURN
    CALCULATE (
        AVERAGE ( TP_ENG_KPI[KPI_VALUE] ),
        FILTER (
            'TP_ENG_KPI',
            'TP_ENG_KPI'[KPI] IN { "MMBF FUN" }
                && TP_ENG_KPI[MonthDate]
                    >= DATE ( YEAR ( _MXDATE ), MONTH ( _MXDATE ) - 12, 1 )
        )
    )