How to check if one value of a combination of values has changed over time

[ChrisLoan]

Hi Everyone!

I have a question for my fellow BI people. I'm gonna use dummy data to give an idea of our Power BI dataset:

So we have a Fact table Invoices with many columns, but the most important ones for my questions are:

• Invoice Number
• Worker Key
• Customer Key
• Date
• IsPayroll?

To give you a visual representation:

My company wants a new column (based on a condition), that has 3 options; SET payroll, NO payroll, VARIABLE payroll.

They wanna know if a certain combination, for example above invoice nr 1 with customer key 20 and worker key 55, has changed it's value in the column "ispayroll" from "n" to "y" over the period of 20 days. If that's the case the new column must be set to "variable". If a combination starts out for the first time with "ispayroll" "Y" the new column value must be set to "Payroll set". If the  "ispayroll" column has a "n" value the new column value must be set to "no payroll". So the result from the example above should be filled in like this:

So the condition must be checked based on the combination of Invoice Nr, Customer Key, Worker Key and Date.

Can anyone help me? I know this might be a little difficult to understand, but any help is welcome! 😉

[v-zhangti]

Hi, @ChrisLoan 

You can try the following methods.

Result = 
IF (
    [payroll?] = "y"
        && [date]
            = CALCULATE (
                MIN ( 'Table'[date] ),
                FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) )
            ),
    "payroll set",
    IF (
        [payroll?] = "n"
            && [date]
                = CALCULATE (
                    MIN ( 'Table'[date] ),
                    FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) )
                ),
        "no payroll",
        IF (
            [payroll?] = "y"
                && [date]
                    > CALCULATE (
                        MIN ( 'Table'[date] ),
                        FILTER ( 'Table', [Invoice number] = EARLIER ( 'Table'[Invoice number] ) )
                    ) + 20,
            "variable",
            IF ( [payroll?] = "n", "no payroll" )
        )
    )
)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[ChrisLoan]

Also, the check has to be based off the combination of invoice number, worker key and customer key. If I add a new line with invoice number  1 but with a different worker key and customer key, it fills it in with "Variable" as well, when in fact it should fill it in with 'Payroll Set':

So sorry, I know what I'm asking is very complicated!

[v-zhangti · Accepted solution]

Hi, @ChrisLoan 

You can try the following methods.

Result =
IF (
    [payroll?] = "y"
        && [date]
            = CALCULATE (
                MIN ( 'Table'[date] ),
                FILTER (
                    'Table',
                    [Invoice number] = EARLIER ( 'Table'[Invoice number] )
                        && [Customer key] = EARLIER ( 'Table'[Customer key] )
                        && [worker key] = EARLIER ( 'Table'[worker key] )
                )
            ),
    "payroll set",
    IF (
        [payroll?] = "n"
            && [date]
                = CALCULATE (
                    MIN ( 'Table'[date] ),
                    FILTER (
                        'Table',
                        [Invoice number] = EARLIER ( 'Table'[Invoice number] )
                            && [Customer key] = EARLIER ( 'Table'[Customer key] )
                            && [worker key] = EARLIER ( 'Table'[worker key] )
                    )
                ),
        "no payroll",
        IF (
            [payroll?] = "y"
                && [date]
                    >= CALCULATE (
                        MIN ( 'Table'[date] ),
                        FILTER (
                            'Table',
                            [Invoice number] = EARLIER ( 'Table'[Invoice number] )
                                && [Customer key] = EARLIER ( 'Table'[Customer key] )
                                && [worker key] = EARLIER ( 'Table'[worker key] )
                        )
                    ) + 20,
            "variable",
            IF ( [payroll?] = "n", "no payroll" )
        )
    )
)

This time the formula is written based on the invoice number, the worker key and the customer key.

(3, 40, 77) obtained the correct value.

As shown in the graph, (1,20,55) has been rejected for the first time after 20 days on March 1, shouldn't it be "variable"? If there are other conditions, please let me know.

Best Regards,

Community Support Team _Charlotte

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

[ChrisLoan]

Hi!

It works! This is exactly what I needed, thanks! 😊

And you're right, it should be "Variable" for that example, I highlighted the wrong row in my screenshot sorry. I meant to highlight the row above, which had the same invoice number, but different customer key and worker key. But that issue has been resolved with the new code you sent me so no worries! 👍

I'm gonna try to implement this in our actual dataset and hopefully everything will work exactly as it does with the dummy data we used here!

[ChrisLoan]

Hi!

Thanks so much for the code, it works and is almost perfect! But unfortunately not entirely as it needs to when I add more data to the dummy data:

As you can see in the example above, there's a blank value? This should have also been filled in with "Variable" as the combination (3, 40, 77) passed 20 days after it's first entry? We're getting close though, this is very promising and almost exactly what I need! 😊

[Greg_Deckler]

@ChrisLoan Try this:

Column =
  VAR __Invoice = [Invoice number]
  VAR __Worker = [worker key]
  VAR __Customer = [customer key]
  VAR __Table = FILTER('Table',[Invoice number] = __Invoice && [worker key] = __Worker && [customer key] = __Customer)
  VAR __Count = COUNTROWS(DISTINCT(SELECTCOLUMNS(__Table,"__Payroll",[payroll?])))
  VAR __Payroll = MAXX(DISTINCT(SELECTCOLUMNS(__Table,"__Payroll",[payroll?])),[__Payroll])
RETURN
  SWITCH(TRUE(),
    __Count > 1, "Variable",
    __Payroll = "n", "No payroll",
    "Payroll set"
  )

[ChrisLoan]

Hey Greg

Is there a way to add the 20 days rule into account in this code? 🙂

[ChrisLoan]

Sorry for the delay, I've been sick and haven't been able to work.

This doesn't work for my questions because it doesn't take in account the 20 day rule that is needed for the "Variable" payroll option.

But it is a nice start because it does a lot of things right! Maybe there's something we can do to add the 20 day rule to your code? 

[ChrisLoan]

Hey Greg!

Thanks so much for the quick response, I'll try the code out tomorrow! 😊