cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
gary_connolly Frequent Visitor
Frequent Visitor

Was this the last payroll transaction this month

Struggling with this one. I have a dataset of transactions that occur either weekly or fortnightly, some manual and some auto, with different types on different phases. I'm trying to flag true/false if the transaction is the last auto transaction for that group for that month:

 

Paygroup          Type           Date        True/False

P1F                 Auto         03/09/17        False

P1F                 Auto         17/09/17        True

P1F                 Manual     17/09/17        False

P1W                Auto         03/09/17       False

P1W                Auto         10/09/17       False

P1W                Auto         17/09/17       False

P1W                Auto         24/09/17       True

P1M                Auto         15/09/17        True

P2F                 Auto         10/10/17        False

P2F                 Auto         24/10/17        True

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Was this the last payroll transaction this month

Hi @gary_connolly,

 

Please create a calculated column with this formula:

True/False =
IF (
    'payroll transaction'[Type] = "Auto"
        && 'payroll transaction'[Date]
            = CALCULATE (
                LASTDATE ( 'payroll transaction'[Date] ),
                ALLEXCEPT (
                    'payroll transaction',
                    'payroll transaction'[Paygroup],
                    'payroll transaction'[Date].[Month],
                    'payroll transaction'[Type]
                )
            ),
    "True",
    "False"
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Was this the last payroll transaction this month

Hi @gary_connolly,

 

Please create a calculated column with this formula:

True/False =
IF (
    'payroll transaction'[Type] = "Auto"
        && 'payroll transaction'[Date]
            = CALCULATE (
                LASTDATE ( 'payroll transaction'[Date] ),
                ALLEXCEPT (
                    'payroll transaction',
                    'payroll transaction'[Paygroup],
                    'payroll transaction'[Date].[Month],
                    'payroll transaction'[Type]
                )
            ),
    "True",
    "False"
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.