Getting the sales value from last month but on the exact weekday

[ChrisA27]

Hi, 

I am wondering anyone can help me out? I am trying to get a sales value from the same day in the previous month. I have date table in place and I need to go back a month and compare the sale for example Wednesday 2nd August against the Wednesday one month ago which would be Wednesday the 5th July. Its a very seasonal business so can't just use same date last month as that won't be the same day and weekend would be busier than week days etc.

As per screenshot I have managed to go back and find the correct date but I am struggling to get a DAX measure to pull back the value at that date.

Essentialy just trying to see what we did on that day the previous month.

Any help would be amazing. 

Thanks

Chris

[Anonymous]

Hi  @ChrisA27 ,

Sorry to reply you now, if you want to change it to a calculated column, you can change max() to the Earlier() function, and the Earlier() function represents the current row in the calculated column.

Refer to:

EARLIER function (DAX) - DAX | Microsoft Learn

next_column =
MAXX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&
    MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))+1&&
    'Table'[Day]=EARLIER('Table'[Day])&&'Table'[Index]=EARLIER('Table'[Index])),[Date])

True_column =
MAXX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&
    MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))-1&&
    'Table'[Day]=EARLIER('Table'[Day])&&'Table'[Index]=EARLIER('Table'[Index])),[Date])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChrisA27]

Hi, 

Thank you so much this is now working as a column. So one last thing I wanted to achieve is creating a visual (Table is fine) of sales values for each of those dates in the column in a side by side view with the current months values. Seems simple yet I can't seem to get it to work. I am used to just using the predefined DAX of PREVIOUSMONTH for example but it won't work for this scenario due to needing the exact day of the week it fell on previously.

Something like:

Date             Sales           Sales 1 Month Previous same day of week

13/09/23  |  1000.00 |             850.00

We are trying to get trends of day of the week in prior months to assist with Rota planning for the next week.

Thanks

[Anonymous]

Hi  @ChrisA27 ,

“show the sales amount for 7th Sept with the sales value from the 3rd August as a side by side”

It's not clear what this text means, do you mean to show both the previous and next month's dates in the current date?

Measure：

next =
MAXX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&
    MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))+1&&
    'Table'[Day]=MAX('Table'[Day])&&'Table'[Index]=MAX('Table'[Index])),[Date])

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChrisA27]

Hi, 

The repsonse actually its super useful for forecasting. Thanks. But what I mean't was I want present like shown below

(Mocked up in Excel)

So based on the Calc and measure you provided I can get dates back fine to find the value but it doesn't let me use the measure in any form of calculations to be able to get the Gross Sales value same day previous month value. Hope that makes sense!

Thanks

Chris

[Anonymous]

Hi  @ChrisA27 ,

Here are the steps you can follow：

1. Create calculated column.

Week =
WEEKNUM('Table'[Date],2)

Day =
WEEKDAY('Table'[Date],2)

Index =
RANKX(
    FILTER(ALL('Table'),    YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])==MONTH(EARLIER('Table'[Date]))&&
    'Table'[Day]=EARLIER('Table'[Day])),'Table'[Date],,ASC)

2. Create measure.

True =
MAXX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&
    MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))-1&&
    'Table'[Day]=MAX('Table'[Day])&&'Table'[Index]=MAX('Table'[Index])),[Date])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[ChrisA27]

Hi, 

This is along the right lines now, how can I make that a calculated column instead of a measure? I the MAXX treats the date differently to a measure so would be interested how you get into column instead?

Thanks

Chris

[ChrisA27]

Hi, Thanks this is correctly picking out the previous dates now, I have spot checked and look good. I cannot seem to use that measure to pull back the sales value from that historical date though.

So I want ideally to show the sales amount for 7th Sept with the sales value from the 3rd August as a side by side. Is that possible?

[mlsx4]

Hi @ChrisA27 

Take a look at these threads: https://community.fabric.microsoft.com/t5/Desktop/Working-with-SAMEPERIODLASTYEAR-on-a-Day-of-Week-b...
https://community.fabric.microsoft.com/t5/Desktop/Comparing-same-period-to-last-year-Monday-TY-to-Mo...