Forum Discussion

sure19's avatar
sure19
Helper 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 (sameperiodlastyear) it calculates till this month last year, where the data is not right.  here is my scenerio.

 

Latest Data:

April2020.

Current Data: June 10th.  Next data is available only on 13th of June.  so when i use this calculation, it takes 

June2019 data to compare with April2020 so the data is not right as it calculates 2 extra month.  how can i resolve this?

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. 

  • 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 )
        )
    )

     

4 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    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
      Helper 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
        Resident 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 )
        )
    )