cancel
Showing results for
Did you mean:
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?

3 ACCEPTED SOLUTIONS
Resident Rockstar

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.

Helper II

Thanks @sturlaws  for this solution, it worked.

Resident Rockstar

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
Resident Rockstar

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.

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.

Resident Rockstar

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

``````

Helper II

Thanks @sturlaws  for this solution, it worked.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors