cancel
Showing results for
Did you mean:
Highlighted
Helper II

## 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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User II

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.

Highlighted
Super User II

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 4
Highlighted
Super User II

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.

Highlighted
Helper II

Thanks @sturlaws  for this solution, it worked.

Highlighted
Helper II

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.

Highlighted
Super User II

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

``````

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors