Variance from last working day

[zubairj]

Hello,

May be i can get help through this channel as i am tired searching for whole day online.

I am new on powerbi

1- I have calendar having field name date also field of WeekInDay from 0 to 7.

2- another table called business table where data exists with business date and Sunday is off so no data for Sunday or holiday

Created measure sum of business

Now wants to have variance from last day but if Sunday or no business date found due to holidays in business table so should show change from last working day. THANKS 

[amitchandak]

@zubairj , I have an blog that will give you rank for last working day

https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

You need columns in the 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)

measures

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

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[AlB]

Hi @zubairj 

You can add a column to your date table to mark the working days, then use that column to look fro the previous working da in your measure. If you provide some sample data fo your tables we could get to a more specific solution.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[zubairj]

Thanks, i did tried this solution also created column name Bdate having condition if weekinday =7 so date-1 else date and linked with measure which is LD=calculate(business),adddate(calendar.bdate)-1 but still result is subtracting with sunday.

Can you help me in writing filter date condition?