Need to get the sum of total auths by month

[Anonymous]

I need to get a calculation to get the sum of total auths by month.  Should I do this in the transformations or in the Reporting functionality via Dax Formula?

[Dangar332]

hi, @Anonymous 

you can do it in DAX

[Anonymous]

Thanks Dangar332,  Can you give an example?

[Dangar332]

Hi, @Anonymous 

Try below measure

Measure = 

Sumx (

     Filter ( tablename, tablename[month column]=earlier(tablename[month column])),

 Sum(tablename[authscolumn]))

[Anonymous]

This was very helpful, but having an error.  Can you tell what I am doing wrong?  The "earlier" function does not seem to want to accept the same table name as the sum/filter function.

[Anonymous · Accepted solution]

Hi @Anonymous ,

 You can create a calculated column or measure to get it using below formulas:

Calculated column:

Auth Fees By Month =
SUMX (
    FILTER (
        'Monthly Billing Archive',
        'Monthly Billing Archive'[Billing / Revenue Recognition Term]
            = EARLIER ( 'Monthly Billing Archive'[Billing / Revenue Recognition Term] )
    ),
    'Monthly Billing Archive'[Auth Fees]
)

Or

Measure:

Auth Fees By Month =
SUMX (
    FILTER (
        ALLSELECTED ( 'Monthly Billing Archive' ),
        'Monthly Billing Archive'[Billing / Revenue Recognition Term]
            = SELECTEDVALUE ( 'Monthly Billing Archive'[Billing / Revenue Recognition Term] )
    ),
    'Monthly Billing Archive'[Auth Fees]
)

Best Regards

[Dangar332]

Hi, @Anonymous 

Sumx (

     Filter ( tablename, tablename[month column]=max(tablename[month column])),

 Sum(tablename[authscolumn]))