topic Re: Same Month Last Year Value not working for Leap year February in DAX Commands and Tips

Same Month Last Year Value not working for Leap year February

gaor — Fri, 04 Mar 2022 03:31:09 GMT

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

Re: Same Month Last Year Value not working for Leap year February

Tanushree_Kapse — Fri, 04 Mar 2022 07:14:39 GMT

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

Re: Same Month Last Year Value not working for Leap year February

Anonymous — Tue, 08 Mar 2022 03:12:04 GMT

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.

Re: Same Month Last Year Value not working for Leap year February

gaor — Tue, 08 Mar 2022 08:44:26 GMT

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