Same Month Last Year Value not working for Leap year February

[gaor]

Hello,

I am calculating the monthly value and the same month last year (it is a measure). I realized that for 2021 Feb, the same month last year (2020 Feb) was not displaying. I tried DATEADD for -12 MONTH and -1 YEAR. I also tried SAMEPERIODLASTYEAR.

Is there any way to solve this? 

Thank you.

Gao

[v-rongtiep-msft]

Hi @gaor ,

Please refer to my pbix file to see if it helps you.

Create a measure and try to use EDATE.

Measure =
VAR previous_ =
    EDATE ( MAX ( 'Table'[date] ), -12 )
RETURN
    CALCULATE (
        MAX ( 'Table'[value] ),
        FILTER ( ALL ( 'Table' ), 'Table'[date] = previous_ )
    )

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gaor · Accepted solution]

Hi Polly,

Thank you for the help. I have tried your methods. There are slight differences in requirements though. I would like to display the values by month not by date. 

On the left I duplicated your example table value for 2nd day of each month.

On the right I simply change the 1st day to end of the month.

Neither works expected.

With your inspiration, I have found the solution, that is, defining the month number and year number.

Corrected Measure = CALCULATE(sum('Table'[value]),FILTER (ALL( 'Table'), ('Table'[date].[Year] = YEAR(MAX ( 'Table'[date] ))-1) && 'Table'[date].[MonthNo]=MONTH(MAX('Table'[date]))))

Thank you.

Regards,

gaor

[Tanushree_Kapse]

Hi, @gaor ,

You need to pull the complete date Field in the table.
Time Intelligence Function works for whole date.

Mark this as a solution if I anwered your question. Kudos are always appreciated.

Thanks