topic Re: Return exact value from same month but from last year. in DAX Commands and Tips

Return exact value from same month but from last year.

agustira97 — Thu, 26 Jan 2023 08:00:35 GMT

I've already make an calculated table in visual studio and it look like this.

CountRows is created using CalculatedColumn where it will count how many rows with the same year and month.
I need to show not just exact same years and month, but also last year and last 2 year in my visualization.

How to make dax measure to show last year and last 2 year without using aggregate like sum? Because CountRows already counted.

I want when my filter year = 2022 ; month = 12 it will show value from 2021-12 for my last year visualisation.

*sorry for my bad english, hope you all can understand.

Re: Return exact value from same month but from last year.

IoannisPhilip — Thu, 26 Jan 2023 09:29:59 GMT

Hi agustira97 ,
you could try using the following:

= CALCULATE(SUM(countrows]), SAMEPERIODLASTYEAR(DateTime[DateKey]))

the SUM() will not alternate the aggregation you expect.
For 2 years, you can try to use instead :

DATEADD(dates, -2, year)

Feel free to adjust the period or the timeframe you want.

I hope that was useful.

Re: Return exact value from same month but from last year.

agustira97 — Mon, 30 Jan 2023 07:51:02 GMT

Thank you for your answer... So I made a new calculated column name 'FullDate' that return date format.
I've also already try run just dateadd, and yeah I got the date that I want.
But when I used it with

CALCULATE(SUM(CalculatedTable1[CountRows]), DATEADD(CalculatedTable1[Full_Date], -1, year)) , the result in my PBI is blank? why?