Get data from the same day of the same week of the same week last year

[vicente89]

I want to get the data for the same day of the same week of the same week last year. Example:

Today is: Wednesday, October 3, week 40 of 2023.

I want: Wednesday (October 2 in this case) of week 40 of 2022.

I have tried the following formulas and they don't work:

- SUM({<fecha_creacion_del_pago={'$(=Date(YearStart(Today()) - 1) + WeekDay(Today()) - WeekDay(Today(1)) - 7))'}>} venta_neta_reserva)

- SUM({<fecha_creacion_del_pago={'$(=Date(WeekStart(Today()) - 365 + WeekDay(Today()) - 1))'}>} venta_neta_reserva)

- SUM({<fecha_creacion_del_pago={'$(=AsOfDate(Today(), -364))'}>} venta_neta_reserva)

In Power BI it could be the following  formula:

"totalsales DATEADD = CALCULATE([Total Sales], dateadd('Date'[Date], -364, DAY))".

[KGalloway]

Would this work?

Here is the formula used in the KPI: sum(if(weekday(date) = weekday(today()) and week(date) = week(today()), amount))

Let me know if I can clarify anything.

[vicente89]

The value is fine, but I would like it to be displayed for each of the days of the week that have passed.

In this case, I would like to get that value but for all the days of the week that have already happened, as they appear in the rest of the columns.

[KGalloway]

Here is a way I found to do this:

First, I created a table in the load script that holds the days of the current week:

this_week:
load
date(weekstart(today()) + iterno() - 1) as day_of_week
autogenerate 1
while date(weekstart(today()) + iterno() - 1) <= weekend(today());

There may be better ways to do this. Loading from an existing calendar with filters to this week would be good if you have that available.

Then, in the app, I used almost the same code to create the table I think you want:

The only change in the formula is replacing today() with day_of_week.

If you don't want to show a value for the days yet to come (like 10/6 and 10/7) you can add another condition to the if statement:

sum(if(weekday(date) = weekday(day_of_week) and week(date) = week(day_of_week) and weekday(date) <= weekday(today()), date))

Let me know if I can clarify anything.

[vicente89]

The problem is that I don't have access to the script editor, so I can't do anything there.
I would need to be able to make a formula to create a measurement that could provide me with that data.

[KGalloway]

Something could be done to replicate that but it is very messy:

I use the same formula with a slight change in each column:
sum(if(weekday(date) = weekday(weekstart(today())+6) and week(date) = week(weekstart(today())+6), amount))

+0 (or nothing) for the first column

+1 for the second

+2 for the third

and so on.

Let me know if I can clarify anything.

[vicente89]

The thing is that in my case (I attach the table in which I would have to fill in the data I am looking for), there are not several columns.It is a single column (so I think I can only put one formula) and seven rows, each corresponding to each day of the week.
I know it is a complicated subject but if you could get me the solution you would help me a lot.
It is a very important data for the company to analyze the income and if I could do it I would look very good with my boss.
Thank you very much for your help.