How to write a dax formula that creates a running total 12 months from the relative date

[dtbingh]

Hi all, 

I'm stumped and having a hard time explaining what the problem is. Here are some pictures to support: 

Basically I want to write formula that will take the 12 month running total of net services to the current month and then divide that by the 12 month running total of chargeable hours to get my average rate.

this table has the correct running total values as filtered by the slicer above. I need to get these numbers into an equation so I can divide the NetSrevicesR13/Total Chargeable Hours and output my Average Rate into a table that has average rate for each month.

I hope that makes sense, please let me know if you have any questions or need clarification. 

Thanks,

[parry2k]

@dtbingh glad you followed the best practice by adding the date dimension. Cheers!!

Do take a moment to subscribe to my YouTube channel where you will find solutions to real-time business problems. Link below.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@dtbingh you are missing one parenthesis before divide sign and you have extra at the end

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[dtbingh]

After making that change to the formula now every month has a value of 161 for average collected rate in 2021

[parry2k · Accepted solution]

@dtbingh there are few issues/improvements required:

first when you are working with dates, as a best practice, add a date dimension to your model and use that for all sort of time intelligence calculations, you can add one following my blog post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutio...

And 2nd, you need to use ALL ( Table Name ) instead of ALL ( Table Name[Year Column] ) but I would highly recommend adding a date dimension in your model.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[dtbingh]

I ended up using the date table query script from enterprise dna and created a new table, then formed the connection in the data model. After doing that then the formula using the all function worked as expected. 

chalking it up to bad data practices. 

[dtbingh]

I'm working on creating that date dimension table, but when I made the other change you mentioned I got this error? "a function 'Filter' has been used ina  true/False expression that is used as a table filter expression. This is not allowed

[parry2k]

@dtbingh what is your formula? Please share that.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[dtbingh]

Here is my formula for average collected rate, if that's what you're asking for.

[parry2k]

@dtbingh not sure if I followed your question 100% but add another measure that calculates the average and then use that in the visual

Avg = DIVIDE ( [Numerator measure], [Denominator measure] )

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[dtbingh]

I have that, and these are the correct numbers for average rate

but the issue is I want to see this average rate for each month. When i put in the date table to do this the formulas break and only calculate the running total for that year. I need the formula to cross over from 2020-2021 in order to have a true 12 month rolling total. 

[dtbingh]

I'm attempting to use this formula to calculate the rolling total of net services for each month 

[Name] is my date table.