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 · Accepted solution]

@zubairj , I have blog how to rank only working days. You can refer.https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

You can have columns like

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)

Then measure like

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]

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

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

[zubairj]

Thanks its working great but 

ODC = [This Day]-[Last day] result is blank

where as This Day & Last Day having values