Getting a previous value from the last work day

[Anonymous]

Hello everyone, im trying to create two card visual, one of them i already did it(that is to create the total amount of the selected date), the second one i need help. Im trying to create a value from the last work day, i already created a holiday table and related to my calendar table(i created a column here i said which days are holidays or weekends) but i cannot figure out how to do this measure works

My sample.pbix

for example, if i select february 4, i want the values of february 3. But if i select february 3 i will see the values of january 31(since february 1 and 2 are days in the weekend) the same will happen if i select february 10(i will get february 7)


Thanks,

[Anonymous]

Thanks for your help @amitchandak , i just changed the formula(again)

Work Date Cont = SWITCH(TRUE(),

[Work Day]=0,maxx(FILTER('CALENDAR',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) && 'CALENDAR'[Work Day] =1 ),[Date])

,[Work Day]=1,maxx(FILTER('CALENDAR',[Date]<EARLIER([Date]) && [Work Day]=1 ) ,[Date]))

and i got exactly what i need

[amitchandak · Accepted solution]

@Anonymous , Have following column in your date table

Work Day = if(WEEKDAY([Date],2)>=6,0,1)
Work Date = if(WEEKDAY([Date],2)>=6,BLANK(),[Date])
Work Date Cont = if([Work Day]=0,maxx(FILTER('Date',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date]),[Date])
Work Date cont Rank = RANKX(ALL('Date'),[Work Date Cont],,ASC,Dense)

refer : https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

then try measure like

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])))
Last work day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Work Date cont Rank]=max('Date'[Work Date cont Rank])-1))
diff =[This Day] - [Last work day]

[Anonymous]

Hey @amitchandak , thanks for your answer that was amazing, but i still have the problem(but now i think its simpler).I used your formula and did a few changes.

Work Date Cont = SWITCH(TRUE(),
[Work Day]=0,maxx(FILTER('CALENDAR',[Date]<EARLIER([Date]) && [Work Day]<> EARLIER([Work Day]) ),[Date])
,WEEKDAY('CALENDAR'[Date],2)=1,[Date]-3,
'CALENDAR'[Date]-1)

and i get this result bellow

that's what i wanted( the last work day before the selected date,that means, the last line with Work Day flag = 1)

But i still have a problem, if i choose February 26 i have to take the values of February 21
(since the carnival here in Brazil was next to a weekend).My problem is to transform that Work Date Cont  in the last date with the Work Day =1