Forum Discussion

zubairj's avatar
zubairj
Regular Visitor
6 years ago

Variance from last working day

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 

 

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      zubairj
      Regular Visitor
      6 years ago

      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?

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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-Calendar-4-5-Power/ba-p/1187766

     

    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.