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 of 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

@gary_connolly,

 

You may refer to the following DAX that adds a calculated column.

Column =
Table1[Type] = "Auto"
    && RANKX (
        FILTER (
            Table1,
            Table1[Paygroup] = EARLIER ( Table1[Paygroup] )
                && Table1[Type] = EARLIER ( Table1[Type] )
                && YEAR ( Table1[Date] ) = YEAR ( EARLIER ( Table1[Date] ) )
                && MONTH ( Table1[Date] ) = MONTH ( EARLIER ( Table1[Date] ) )
        ),
        Table1[Date],
        ,
        DESC,
        DENSE
    )
        = 1
Community Support Team _ Sam Zha
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

@gary_connolly,

 

You may refer to the following DAX that adds a calculated column.

Column =
Table1[Type] = "Auto"
    && RANKX (
        FILTER (
            Table1,
            Table1[Paygroup] = EARLIER ( Table1[Paygroup] )
                && Table1[Type] = EARLIER ( Table1[Type] )
                && YEAR ( Table1[Date] ) = YEAR ( EARLIER ( Table1[Date] ) )
                && MONTH ( Table1[Date] ) = MONTH ( EARLIER ( Table1[Date] ) )
        ),
        Table1[Date],
        ,
        DESC,
        DENSE
    )
        = 1
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.