Forum Discussion
How to get previous date from fact table if max date is weekend in power bi
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
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 ) )
2 Replies
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 ) )