Forum Discussion

New_be's avatar
New_be
Icon for Helper V rankHelper V
5 years ago
Solved

Yesterday value: Multiple conditions

Hi everyone, i've been in a quite difficult situation right now. Having problems to get yesterday (YDA) value based on multiple conditions. It is quite hard to explain, but i will try my best so that this problem will be clear for you all.

 

NORMAL CONDITIONS:

In normal day, my YDA value condition is just check for the day off. For example;

1. For example if today is 12/11 Thu, my YDA value will be 11/11 Wed.

2. For example if today is 12/11 Thu, my YDA value will be 11/11 Wed, but on 11/11 is a day off, so my YDA value will be on 10/11 Tue.

3. For example today is 16/11 Mon, so my YDA value should be from 13/11 Fri because 15 Sun & 14 Sat is day off (weekend).

 

So my code for these conditions is;

MINX(
   FILTER(
      'Calendar',
      'Calendar'[DATE] > EARLIER('Calendar'[DATE]) && 'Calendar'[DATE] <> 'Calendar'[DAY_OFF]
   ),
   'Calendar'[DATE] )
 
I also have column that check date = today is true. Meaning that, if today 12/11 & is not day off, it will return true. This code works fine with above conditions.
 
------------------------------------------------------------------------------------------------------------------------------------
The real problem is here, my company will shut down the operation for production because they want to expand the operation plant.
 
So in my company day off calendar, we will start off from 13/11 untill 22/11. But this is for those who are directly involve with the operation. For those who are working at office, we still need to come to work, including my plant manager that will use this report.
 
PLANT EXPANDATION CONDITION:
1. If i use the previous code for YDA (refer above code), my report will show blank because in day off calendar, today is day off. And we can only see YDA Value in my report starting from 23/11 that will show data from 12/11. (Because based on my code, if date = today & is not day off  will return true. But in this case, today is still 16/11 meaning that is off day, so the value for YDA will not show up)
 
2. So how can i combine both normal condition & Plant expandation condition together?
 
Below is attached example for your reference. Sorry for typing too long. Hope with this explaination, you can have a better understanding for my problems. Thanks in advance!
 
Need Help
Show and Tell
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    New_be , I have a blog on how to work with working days that can help

     

    You need few columns out of these add, as per need 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)
Work day of month = Sumx(filter(Date, [Month year] = earlier([Month Day])),[Work Day])
Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])

Month Work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && 'Date'[Work day of month]  <= Max('Date'[Work day of month]) ))
last Month Work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1  && 'Date'[Work day of month]  <= Max('Date'[Work day of month]) ))
Week till work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Work day of week]  <= Max('Date'[Work day of week])))
last Week till work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[Work day of week]  <= Max('Date'[Work day of week])))

     

    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]

     

    Traveling Across Workdays - What is next/previous Working day
    https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766

2 Replies

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

    New_be , I have a blog on how to work with working days that can help

     

    You need few columns out of these add, as per need 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)
Work day of month = Sumx(filter(Date, [Month year] = earlier([Month Day])),[Work Day])
Work day of week = Sumx(filter(Date, [Weekyear] = earlier([Weekyear])),[Work Day])

Month Work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && 'Date'[Work day of month]  <= Max('Date'[Work day of month]) ))
last Month Work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1  && 'Date'[Work day of month]  <= Max('Date'[Work day of month]) ))
Week till work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Work day of week]  <= Max('Date'[Work day of week])))
last Week till work day= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[Work day of week]  <= Max('Date'[Work day of week])))

     

    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]

     

    Traveling Across Workdays - What is next/previous Working day
    https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calendar-4-5-Power/ba-p/1187766