power query M equivalent of SAMEPERIODLASTYEAR

[refex]

In my report I have a measure in DAX built in this way:

MonthlyValuePreviousYear = CALCULATE(SUM('myData'[MonthlyValue]), SAMEPERIODLASTYEAR('myData'[Timestamp]))

for some needs which I have to conform to this logic must be moved in power query so written in M language, I just started with power bi and DAX, let alone M... so I searched a bit but could not find a suitable example online.

Is it possible to convert the logic above in M to create a calculated column that shows in the same row the value of the previous year (exacly 12 month before)?

[watkinnc · Accepted solution]

Add a column:

= Table.AddColumn(TableName, "LastYearDate", each Date.AddYears([DateColumn, -1))

If I named that step LastYear, then I would add a join:

Table.Join(TableName, {"LastYearDate"}, LastStep, {"LastYearDate"}, JoinKind.LeftOuter)

Now your Current Dates are lined up with last years dates, along with everything from those rows.

--Nate

[refex]

It solved for me, thank you.

I'm avoiding to do this in DAX cause I need to dynamically rename the MonthlyValue based on other values and I managed to do this only in power query. Otherwise I wouldd-ve stuck with dax 

[BA_Pete]

Hi @refex ,

Did my answer work for you?

If so, please consider marking it as the solution to help others find the answer quicker.

Thanks,

Pete

[BA_Pete]

Hi @refex ,

Try:

Date.AddYears([Date], -1)

Pete

[refex]

Hi @BA_Pete, thank you for your answer.

With your suggestion I can build a date that goes back exaclty one year, but this does not give me the value of the field "MonthlyValue" on that date

[BA_Pete]

@refex ,

Indeed, it doesn't. That's what DAX is for.

Smart-alec responses aside, if you REALLY want to do this in Power Query, then you would do something like this:

1) Aggregate (group) your table by relevant dimensions and date i.e. you would have only a single row for each date/dimension combination, not multiple transactions within each date.

2) Merge all row-unique dimension fields into a single field. This may be [Department], [Client], [Sale Date] etc. so you would end up with a single field that looks something like this:

Sportwear-ABC Co.-05/06/2021

3) You would then do the same again, but use your prior year date field instead.

4) Self-merge the query on itself on [Merged Field Prior Year] LEFT OUTER [Merged Field Current Year]. This should then add the prior year date row for that combination of dimensions onto the 'current' year row. This would apply to any date that had values for that dimension combination on exactly the same date in the year prior.

Hopefully you can see quite quickly that Power Query/M is not the place to be doing things like this. It really is what DAX is entirely designed for/to do.

Pete