SAMEPERIOD Lastyear

[sure19]

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?

[sturlaws · Accepted solution]

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 · Accepted solution]

Thanks @sturlaws  for this solution, it worked.

[sturlaws · Accepted solution]

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]

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 · Accepted solution]

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 · Accepted solution]

Thanks @sturlaws  for this solution, it worked.