CODEWITHSUNDEEP
datepowerbidaxperiod
Lee M
Lee M

Reputation: 11

I need a measure in PowerBI gives the equivalent weekday vs the same WEEKDAY of the previous year

I have a weekday vs previous year weekday requirement that does not seem to be common in the Retail industry. I have found no articles or videos that happens to address this issue. My employer wants to compare the first Monday of each month to the first Monday of the same month, previous year, the first Tuesday, Wednesday, etc., for the entire month. I have a Dates table (marked as such) that relates to my Orders table. I have already come up with a formula for getting the proper date of the previous year. It is a calculated column in the Dates table called Offset_Date. So May 1, 2024 (Wed) DATE equates to May 3, 2023 (Wed) Offset_Date. So this part I have already figured out. The problem is that I can't seem to get this year'ss Daily Sales Total and the equivalent Daily Sales Total LY for the equivalent date in the previous year.

As I said, I already have the correct date as Offset date. In the sales transaction table I have a measure for

Daily_Total = SUM(Sales[TotalPrice])

I added a measure for

Daily_Total_LY = 
CALCULATE(
    SUM(Sales[TotalPrice]), 
    Dates[Offset_Date]
)

however it DOES NOT give me the sum from the previous year. It give the same sum as this year. I have also tried

Daily_Total_LY = 
CALCULATE(
    SUMX(Sales, SUM(Sales[TotalPrice])), 
    DateTable[Offset_Date]
)

However that returns a huge number, implying it isn't using Offset_date as a filter.

I suspect the first formula returns the same values because it is the Date column that relates to the Sales table, so Offset_date is irrelevant. I haven't a clue on the 2nd formula. Any direction would be appreciated.

Upvotes: 1

Views: 99

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Can you please try this below measure-

Daily_Total_LY = 

var this_row_offset_date = min(Dates[Offset_Date])

RETURN
CALCULATE(
    SUM(Sales[TotalPrice]), 
    FILTER(
        ALL([Sales]),
        Dates[Date] = this_row_offset_date
    )    
)

Upvotes: 0

Related Questions