Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Getting a previous value from the last work day

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 fro...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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