Date Filter with WTD, MTD, STD, YTD Calculations

[mv2019]

Hello,

I am trying to calculate WTD, MTD, STD, YTD Sales using the following code: 

WTD Sales = if(LOOKUPVALUE(Dim_Day[Week Number],Dim_Day[Day],Sales[Week End Date])=lookupvalue(Dim_Day[Week Number],Dim_Day[day],today())-1,Sales[Total Sales],0)

I have sales data for the past 3 years. My years start and stop based on a customer calendar. Sales are only recorded only a weekly basis (Saturdays).

Data is recorded one week behind so that is why I indicated -1 after today(). This code was working fine a few days ago for WTD from last week but now it's not working at all. Not sure what happened. 

In addition, I want users to be able to navigate between weeks. That is, be able to see what WTD sales were depending on the week they choose. 

Can someone help with this? Let me know if you require more info in order to help.

Thank you in advance.

[v-cherch-msft · Accepted solution]

Hi @mv2019 

Please check if below measure could help.If it is not your case,please share some sample data and expected output which would be helpful to provide an accurate solution.You can also upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Reference post:https://community.powerbi.com/t5/Desktop/Relative-WTD-MTD-amp-YTD-values/td-p/209139

How to Get Your Question Answered Quickly

WTD :=
CALCULATE (
    SUM ( Table1[Sales] ),
    DATESINPERIOD (
        Table1[Date],
        LASTDATE ( Table1[Date] ),
        - WEEKDAY ( LASTDATE ( Table1[Date] ) ),
        DAY
    )
)

 Regards,