Lowest value in Profit% by Date Column excluding any holidays

[Anonymous]

I have Data like:

Date      Profit% City

21mar20 33.3  Paris

15mar20 100 New york

18mar20 67 Paris

18may21 67 New york

21may21 67 Berlin

22may21 33 Berlin

16may21 21 Paris

I want to get the Date in which Profit % was the lowest City wise and it should exlcude holidays.

So if there is Profit% for a day which is satuday or sunday it should get excluded.

So here for paris ,21 % will get ignored as 16may21 is Sunday.

So Results should look like

21mar20 33.3 Paris

18may21 67 New york

22may21 33 Berlin

[v-yalanwu-msft]

Hi @Anonymous  , 

You could create a measure as follows:

lowProfit% =
CALCULATE (
    MIN ( [Profit%] ),
    FILTER (
        ALLEXCEPT ( 'Data', 'Data'[City] ),
        WEEKDAY ( [Date], 2 ) <= 5
            && WEEKDAY ( [Date], 2 ) >= 1))

 Then create a flag measure and apply it into filter:

flag =
IF (
    WEEKDAY ( MAX ( [Date] ), 2 ) >= 1
        && WEEKDAY ( MAX ( [Date] ), 2 ) <= 5,
    1)

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  

[Ashish_Mathur · Accepted solution]

Hi,

Your answers are wrong - please recheck.  You may download my PBI file from here.

Hope this helps.