Calculate same day of today but last year

[maximax77]

Good day,

I need to calculate the number of passage of people through a gate on the same day of today but of last year.
I have the timestamp of the passage in this format
My table (Passage) is like this:

06/06/2019 10:04:15	1
06/06/2019 10:04:18	1

<tbody>
</tbody>

I would like to know how many people passed in total last year the 6th of June
I understood I have to work with calculations but I am not able to figure out how to obtain a value in format dd/mm/yyyy that I can use to filter the main table Passage.

Thanks in advance for helping.

 

[pinarello]

I don't think your idea's fully developed yet. Because Monday the 3rd of June last year was a Sunday. and in leap years you have a 2-day shift of the weekday. It is better to subtract 364 from the date in normal years

 

[maximax77]

I understand what you mean but i don't need to know if it is a Sunday or another day, i could also subtract 365 it is not a problem, my problem is how to subtract to the date i have

 

[pinarello]

If you like you can take a look into the workbook I'v created

Link to OneDrive

or use the PQ M-Code

let    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Changed_type = Table.TransformColumnTypes(Quelle,{{"Date", type date}, {"time", type time}, {"Counter", Int64.Type}}),
    Userdef_col = Table.AddColumn(Changed_type, "Year(now)", each Date.Year(DateTime.LocalNow())),
    Userdef_col2 = Table.AddColumn(Userdef_col, "Year", each Date.Year([Date])),
    Userdef_col3 = Table.AddColumn(Userdef_col2, "Month", each Date.Month([Date])),
    Userdef_col4 = Table.AddColumn(Userdef_col3, "Day", each Date.Day([Date])),
    Userdef_col5 = Table.AddColumn(Userdef_col4, "Passage_CY", each if[#"Year(now)"] = [Year] then [Counter] else null),
    Userdef_col6 = Table.AddColumn(Userdef_col5, "Passage_LY", each if [#"Year(now)"] - 1 = [Year] then [Counter] else null),
    Grouped_lines = Table.Group(Userdef_col6, {"Month", "Day"}, {{"Current year", each List.Sum([Passage_CY]), type number}, {"Last year", each List.Sum([Passage_LY]), type number}}),
    Sorted_Lines = Table.Sort(Grouped_lines,{{"Month", Order.Ascending}, {"Day", Order.Ascending}})
in
    Sorted_Lines

 

[maximax77]

Thanks! will take a look