How to get previous date from fact table if max date is weekend in power bi

[Anonymous]

HI Team

can you please let me know i just need to sum of Total Length as per max date and Previous date

SP	Total Length	Date
A	1	8/07/2021
B	2	8/07/2021
C	3	8/07/2021
A	4	9/07/2021
B	5	9/07/2021
C	6	9/07/2021
A	7	12/07/2021
B	8	12/07/2021
C	9	12/07/2021

Like if I use max(Date)-1 it shows 11/7/21 which is weekend then it should select 9/7/21 for filtering.

I am using this meassure

Previous_Total_Length =

CALCULATE(

[TTD],

FILTER(ALL(Sheet1[Download Date]),

Sheet1[Download Date]= MAX(Sheet1[Download Date])-3

)

)

so if MAX(Sheet1[Download Date])-1 is weekend then i have to do -3 and if not then -1. but i dont want manually change -3 and -1. 

can anyone help me out.

heaps thanks

[Vera_33 · Accepted solution]

Hi @Anonymous 

You asked a question in Power Query, but you need a DAX measure, right? Is your Sheet1 the same fact table as your sample data? Try to identify weekday first

Previous_Total_Length =
VAR CurDate =
    MAX ( Sheet1[Download Date] )
VAR CurDay =
    WEEKDAY ( CurDate, 2 )
VAR PreDate =
    SWITCH (
        TRUE (),
        CurDay = 1, CurDate - 3,
        CurDay = 7, CurDate - 2,
        CurDate - 1
    )
RETURN
    CALCULATE (
        [TTD],
        FILTER ( ALL ( Sheet1[Download Date] ), Sheet1[Download Date] = PreDate )
    )

[Anonymous]

HI Vera_33, I really appriciate your  support. to get this to be done I strugged a lot. now get sorted. Heaps thanks