topic Re: Need to get the sum of total auths by month in DAX Commands and Tips

Need to get the sum of total auths by month

Anonymous — Fri, 05 Jan 2024 18:19:59 GMT

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?

Re: Need to get the sum of total auths by month

Dangar332 — Fri, 05 Jan 2024 18:25:49 GMT

hi, Anonymous

you can do it in DAX

Re: Need to get the sum of total auths by month

Anonymous — Fri, 05 Jan 2024 21:08:19 GMT

Thanks Dangar332, Can you give an example?

Re: Need to get the sum of total auths by month

Dangar332 — Sat, 06 Jan 2024 05:04:22 GMT

Hi, Anonymous

Try below measure

Measure =

Sumx (

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

Sum(tablename[authscolumn]))

Re: Need to get the sum of total auths by month

Anonymous — Mon, 08 Jan 2024 22:33:39 GMT

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.

Re: Need to get the sum of total auths by month

Dangar332 — Tue, 09 Jan 2024 05:54:18 GMT

Hi, Anonymous

Sumx (

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

Sum(tablename[authscolumn]))

Re: Need to get the sum of total auths by month

Anonymous — Tue, 09 Jan 2024 08:29:53 GMT

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] )

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