Yesterday value: Multiple conditions

[New_be]

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!

[amitchandak · Accepted solution]

@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-Calenda...

[New_be]

Thanks your your fast reply Mr @amitchandak ! i will try one by one. Thanks!