Calculate a measure using two or more conditions

[Anonymous]

Hi, everyone.

I am quite new to Power BI and need help with calculating a measure using several conditions. 

I have an aggregated table of orders listing different information about them, like shipping method, client type, date and the client/company the order belongs to.

I need to calculate two measures that I cannot figure out. Try to do google it and it didn't help. The measure I need are:

1) Backlog in days: this is the number of days the orders reach the status completed. I have several status values which I would need to include as a nested if condition or the like. I need to calculate backlog for both b2b and b2c orders differently. 

I define the backlog as follows: the number of days the orders stay in one of the statuses "packed", "picked", "bloked", i.e. unless it changes to completed, not taking into account the weekend days. So it should be today minus whatever day where there is an order with one of the above mentioned statuses (ideally, when the number of such orders exceeds 20-30 on this particular day). 

Hier ist the source table I have:

Client	Order_number	Status	Shipping_type	Client_type	Date	Number of positions
Company 1	Order1	shipped	dhl	b2c	01.03.2021	5
Company 2	Order2	packed	dpd	b2b	15.03.2021	1
Copamny 3	Order3	picked	dpd	b2b	16.03.2021	1
Company 1	Order4	blocked	dhl	b2b	17.03.2021	2
Company 2	Order5	picked	dhl	b2b	18.03.2021	1
Copamny 3	Order6	packed	dhl	b2c	01.04.2021	4
Company 1	Order7	shipped	dhl	b2c	13.04.2021	16
Company 2	Order8	packed	dpd	b2c	14.04.2021	14
Copamny 3	Order1	picked	dpd	b2c	15.04.2021	1

2) I also have another table with different KPIs assigned to each day where I have a column of backlog. It is the backlog on this particular day which is calculated once daily when the orders are trasmitted to us. I need to exctract the backlog for today, that is the last entry (every day a corresponding row is added to the table with today's date). The functions like lastnonblank or maxx don't seem to work in my case (or at least, I can't figure it out given my littel knowledge of DAX). 

Any help and tipp would be very much appreciated.

Regards  

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

For your question1, I made a sample, please check.

I made a calendar table and add a column about the day of the week.

Table 2 = ADDCOLUMNS(CALENDAR(DATE(2021,1,1),DATE(2021,12,31)),"Weekday",WEEKDAY([Date],2))

Then create the measure

Backlog in days =
VAR StartDate =
    CALCULATE (
        MIN ( 'Table'[Date] ),
        ALLEXCEPT ( 'Table', 'Table'[Client] ),
        'Table'[Status] <> "completed"
    )
VAR EndDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        'Table'[Status] = "completed",
        ALLEXCEPT ( 'Table', 'Table'[Client] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table 2' ),
        FILTER (
            'Table 2',
            [Date] >= StartDate
                && [Date] <= EndDate
                && [Weekday] <> 7
                && [Weekday] <> 6
        )
    )

For your question 2, please provide more details or share me with your pbix file.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I'd suggest starting simple and build your measure out step by step. First I'd start with working out the number of days  between today and the date listed: =DATEDIFF(TODAY(),[DATE],DAY). Once you get that then you can exclude weekends. First step is to create a Date Table that has a calculated column to indicate whether each day is a weekday or not (week day = 1, weekend day = 0). Create a date table by following the link above and call it Dates. Then add the calculated column called IsWorkDay by using this formula: 

IsWorkDay=SWITCH(WEEKDAY([Date]),1,0,7,0,1)

Then you can update your measure to look like:

Backlog in Days=CALCULATE(SUM(Dates[IsWorkDAY]),
                                                                                       DATESBETWEEN(Dates[Date],
                                                                                          BacklogTable[Date],
                                                                                              Today())
                                                                                      )

Finally start adding in your filters by using the CALCULATE function:

=CALCULATE(SUM(Dates[IsWorkDAY], DATESBETWEEN(Dates[Date],BacklogTable[Date],Today())),[Status] IN {"packed", "picked", "bloked"})

That should get you started at least.

[Anonymous]

Hi, @Anonymous .

Thanks for the quick reply. 

Unfortunately I bumped into some errors when I tried to implement the codes you provided.

First, I tried to calculate a measure as per the formula you gave me but got an error "A single value for column 'Date' in table 'BacklogTable' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.". I guess I was trying to calculate a measure instead of a column so I went forward and created a calculated column instead. 

In the next step, I tried to calculate a measure with the combination of calculate&sum as per the formula but again came across an erre "Too many arguments were passed to the SUM function. The maximum argument count for the function is 1." I don't understand what I did wrong. 

I would really appreciate further help.

Regards,

[Anonymous]

My apologies, rather than a measure try to use the calculation in a calculated column in the first step and once you get the days worked out that adjust to exclude the weekends