Time Intelligence: Related time period (by week, weekday) for current year month.

[Latte]

Hello!

This far I have found a solution to my problems with DAX in these forums but now I’m really stuck. I’m using Excel Power Pivot, but will be using PowerBI (hopefully) in near future.

I’m building a simply daily report having i.e. MTD sales current year and for comparison the sales in related time period previous year: same week, same weekday. For example for the January 2024 the relevant comparison should be from 2nd of January 2023 to 1st of February. I cannot use ISO weeks as the period of current year must be the real month.

I found a nice formula to calculate sales per comparison date, but couldn’t find out a way to calculate the cumulative totals as I can’t use MTD, nor SAMEPERIODLASTYEAR for previous year. DATEADD maybe works but I'm a bit worried about its proofness in becoming years.

Tried several ways, best tries below.

This is something that would probably work, but I could not calculate the start of the relevant period when row context wasn’t the first day of the current month:

CALCULATE ( [Total_sales], DATESBETWEEN ( DateTable[Dates] , “start of the relevant period”, “end of the relevant period”)

Tried to calculate the comparison date of the first date of the current month with nested calculate but it produces the date only to the spe:

CALCULATE (

CALCULATE ( LASTDATE(DateTable[Date] ),

FILTER ( ALLSELECTED (DateTable), DateTable[Year] = MAX(DateTable[Year]) -1 ),

FILTER ( ALLSELECTED (DateTable), DateTable[Week] = MAX (DateTable[Week]) ),

VALUES (DateTable[WeekDay]))

, FILTER ( ALLSELECTED (DateTable), DateTable[date] = STARTOFMONTH (DateTable[dates]) )

Then another try with calculating a table with comparison dates for all mtd dates for current year:
CALCULATETABLE ( DATESMTD (DateTable[dates]),

FILTER ( ALLSELECTED ( DateTable), DateTable[dates] = CALCULATE (LASTDATE(Sales[SalesDate]),

FILTER(ALLSELECTED(DateTable), DateTable[Year] = MAX(DateTable[Year])-1),

FILTER(ALLSELECTED(DateTable), DateTable[Week] = MAX(DateTable[Week])), VALUES(DateTable[WeekDay])

)

This produces only blanks.

There must be a rather simply and sophisticated way to do this. Can anyone help with this?

[Latte · Accepted solution]

Allright, I got it to work. The solution was actually the way I reasoned it at the beginning. The measure to calculate the the comparison date (last year) and its sales was a bit wrongly constructed, it worked for single row but not for table calculations (sumx). I had put the date table of sales table as one of the date column in the formula, and the "filter" of values of days of the week had to be positioned differently.

The working measures

1. Measure: This will calculate total sales or the comparison date (= same day of the week on same week last year. Example: for 1st Jan 2024 the comparison date is 2nd Jan 2023 and so on.
   
   Sales_LY_ComparisonDate=

CALCULATE (

        SUM (SalesTable[sales]),

        FILTER (

                ALL (DateTable), DateTable[Date] =

                       CALCULATE ( LASTDATE (DateTable[Date]),

                               FILTER ( ALLSELECTED (DateTable), DateTable[Year] = MAX ( DateTable[Year]) – 1 ),

                               FILTER ( ALLSELECTED (DateTable), DateTable[WeekNum] = MAX ( DateTable[WeekNum] ) ),

                               VALUES ( DateTable[DayOfTheWeek]) ) ) )

2. Measure: This applies the calculation above to all rows needed for MTD and sums the totals up.

Comparison_period_cumulative_sales_LY =

SUMX ( DATESMTD ( DateTable[Date] ), [Sales_LY_ComparisonDate] )

[lbendlin]

Looks like you are trying to implement "YoYTD".  You have to combine two filters.  

- DATEADD(xxx,-1,YEAR)

- EDATE(MTD cutoff date,-12)

Note that this will produce skewed results for (at least)  the first six days of each month  as the weekdays won't match.

[Latte]

Thank you Ibendlin for quick answer!

For the business in my case the comparison between same weekday is essential. Thus, that would not work.

I finally succeeded with the challenge. I'll post it in another message.