Merge Values from same Dimension - Last Week, This vs. Last Year

[MacGyver]

Hi Foks!

Hope to get an solution - i am still working on for 2 days 😞

I have a dimension "Invoicedate". It's formatted as date and autocalendar is working great.
Hint: We did not have an invoice on each day! Only monday to friday. So there is no value on a weekend.
Now i need to get this Dimension working for a Table Diagramm.

I want to get a table to compare our sales volume at last week against last week in previous year.

Sample Result:

So my problem is - if there is no Invoice on weekend - there is no value as an row in my dimension. My idea was to merge both dateranges from 2023 and 2022, to get a full list of days from 20.11. to 26.11. in order to calculate the sales volume on this day (of this year).

My last formular idea was this:

=date(	aggr(
    	only({1<[InvoiceDate.autoCalendar.Week]={"W$(=vWeek)"},[InvoiceDate.autoCalendar.Year]={"$(vActualYear)","$(vPreviousYear)"}>}[InvoiceDate]),
	[InvoiceDate]
    ),'DD.MM.')

 I am so close... 😉

My Result with this formular:

As you can see - all Dates come together - by year. But i need them to combine to get the range from 13.11. (starting in '22) to 19.11. (Ending in '23). (*Dont be confused: This screenshot is a week before my first screenshot)

I hope anybody understands my thoughts and needs. Does anyone got an idea how to handle this?

Regards from Germany.

Mirco

[vincent_ardiet_]

If you have access to the loading script, this would be easier to add the missing days there I think.

[MacGyver]

Mh, loading script is possible.

In which way would you do this? Creating a new Dimension - just a bunch of day in last 3 years !?

It there a way for a loop script to create this inline table?

[vincent_ardiet_]

If you have access to the script, you can add the missing dates for example.

[panosalexand · Accepted solution]

Hi Micro,

Please try this through the script and merge this Date within a master calendar.

Load *,

Date(Date#(InvoiceDate,'YYYY.MM'),'YYYYMMDD') as Date

From ......

Hope this is your request. 

Regards,

Panos

[Aasir]

=date(aggr(
only({1<[InvoiceDate.autoCalendar.Week]={"W$(=vWeek)"}, [InvoiceDate.autoCalendar.Year]={"$(vActualYear)","$(vPreviousYear)"}>} WeekStart([InvoiceDate])),
WeekStart([InvoiceDate])
), 'DD.MM.')

[marcus_sommer]

The simples approach would be to add a date-field without a year-information within the calendar, like:

dual(day(Date) & '.' & month(Date), daynumberofyear(Date)) as DateWithoutYear

This is then the vertical dimension and your Year field becomes the horizontal dimension und sum(Sales) would work as expression. Instead of Year as horizontal dimension you may also use n expressions and define the years within a set analysis.

With more as two years included there should be no weekend-gaps but gaps in regard to holidays and/or other exceptions are further possible. If you really want to prevent any gaps you will need to populate the missing data within the fact-table.

[MacGyver]

Thanks to all contributers an their solutions. It was hard to find a possible way.

The best solution to me is marked and inspired me to solve my problem.

Thanks all.