How to obtain the SAMEPERIODLASTYEAR functionality for a given year that does not change?

[Anonymous]

Hello everybody,

This is the first time I write in the Power BI Community.

So, my problem is the following:

I want to find the monthly percentage variation of values for Year X with:

1) Previous Year

2) Year 2019

I applied the following formulas:

1) Elec kWh Y-1 = CALCULATE(SUM(Elec[Elec kWh]),SAMEPERIODLASTYEAR(DimDate[Date]),DimDate[IsPast]=TRUE())

2) Elec kWh 2019 = CALCULATE(SUM(Elec[Elec kWh]),DatesBetween(Elec[Date], Date(2019,01,01), Date(2019,12,31)))

I want to find the variation on Year 2020, 2021 and 2022 with Previous Year and Year 2019. So, I applied the following formulas:

1) Elec kWh %Y-1 = DIVIDE(SUM(Elec[Elec kWh])-[Elec kWh Y-1],[Elec kWh Y-1])

2) Elec kWh %2019 = DIVIDE(SUM(Elec[Elec kWh])-[Elec kWh 2019],[Elec kWh 2019])

I want now to be able to select from a slicer a given year and see the monthly variation. I obtain:

1) All good. It works with Previous Year.

2) It doesn't work with Year 2019. It returns blank values.

My understanding of the problem is that when I filter for Year 2020 or 2021 or 2022 through the slicer, then this formula:

Elec kWh 2019 = CALCULATE(SUM(Elec[Elec kWh]),DatesBetween(Elec[Date], Date(2019,01,01), Date(2019,12,31)))

is unable to work properly because the filter on the slicer does not let Power BI finds the 2019 values since they got filtered out.

So, time functions such as SAMEPERIODLASTYEAR, PARALLELPERIOD, DATEADD works great, but unfortunately none of these functions let you set a static year. Furthermore, the function DATESBETWEEN is not fit for the case since it gets no values due to the filter on the slicer for a different period.

I have tried functions such as ALL and ALLCROSSFILTERED to remove the filter on the slicer when calculating Elec kWh 2019. However, even though they remove any filters on Elec kWh 2019, they only provide the total value for the year 2019 rather than the 2019 monthly values. So, I end up dividing (Jan-21 - Tot-19) / Tot-19 rather than (Jan-21 - Jan-19) / Jan-19.

So, is there any function similar to SAMEPERIODLASTYEAR or PARALLELPERIOD or DATEADD that make you set a static period (in my case year 2019)?

Thank you!

Hope to find an answer to this problem!

Cheers,
Dodo

[amitchandak · Accepted solution]

@Anonymous ,

First of all use date table

for 2019 static measure
= Elec kWh 2019 = CALCULATE(SUM(Elec[Elec kWh]),filter(all('Date'), year('Date'[Date]) =2019))

or

2019 (using any selected year) =
Var _diff = year(maxx(allselected('Date'), 'Date'[Date])) -2019 +1
return
CALCULATE(SUM(Elec[Elec kWh]),SAMEPERIODLASTYEAR(dateadd('Date'[Date],-1*diff, year)))

or

2019 (using any selected year) =
Var _diff = year(maxx(allselected('Date'), 'Date'[Date])) -2019
return
CALCULATE(SUM(Elec[Elec kWh]),dateadd('Date'[Date],-1*diff, year))

same way other year

= Elec kWh 2019 = CALCULATE(SUM(Elec[Elec kWh]),SAMEPERIODLASTYEAR('Date'[Date]))

[amitchandak · Accepted solution]

@Anonymous ,

First of all use date table

for 2019 static measure
= Elec kWh 2019 = CALCULATE(SUM(Elec[Elec kWh]),filter(all('Date'), year('Date'[Date]) =2019))

or

2019 (using any selected year) =
Var _diff = year(maxx(allselected('Date'), 'Date'[Date])) -2019 +1
return
CALCULATE(SUM(Elec[Elec kWh]),SAMEPERIODLASTYEAR(dateadd('Date'[Date],-1*diff, year)))

or

2019 (using any selected year) =
Var _diff = year(maxx(allselected('Date'), 'Date'[Date])) -2019
return
CALCULATE(SUM(Elec[Elec kWh]),dateadd('Date'[Date],-1*diff, year))

same way other year

= Elec kWh 2019 = CALCULATE(SUM(Elec[Elec kWh]),SAMEPERIODLASTYEAR('Date'[Date]))

[Anonymous · Accepted solution]

@amitchandak

Thank you it worked!

I will just rewriting the correct formulas below since yours contained some small typo mistakes.

So the correct formulas were these 2:

2019 (Option 1) =

VAR diff = YEAR(MAXX(ALLSELECTED(DimDate), DimDate[Date])) - 2019 - 1

RETURN

CALCULATE(SUM(Elec[Elec kWh]),SAMEPERIODLASTYEAR(DATEADD(DimDate[Date],-1*diff, YEAR)))

2019 (Option 2) =

VAR diff = YEAR(MAXX(ALLSELECTED(DimDate), DimDate[Date])) - 2019

RETURN

CALCULATE(SUM(Elec[Elec kWh]),DATEADD(DimDate[Date],-1*diff, YEAR))

I will go with 2019 (Option 2) since its formulation looks less redundant.

Thank you again!

Regards,

Dodo

[v-janeyg-msft]

Hi, @Anonymous 

Sorry, I'm trying to understand your question, but without a data sample and your formulas are not clearly wrong, it's hard for me to judge. I think that's why we don't respond well to you.

Check this first:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

The time intelligence function is indeed prone to errors, and generally we don't need to use them.

Try:

measure =
CALCULATE (
    SUM ( Elec[Elec kWh] ),
    RELATED ( Elec[Date] ) >= DATE ( 2019, 01, 01 )
        && RELATED ( Elec[Date] ) <= DATE ( 2019, 12, 31 )
)

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey