topic Re: Looking to get a rolling 12 months on a measure column in DAX Commands and Tips

Looking to get a rolling 12 months on a measure column

gypsi96 — Fri, 10 May 2024 18:56:31 GMT

I'm having the hardest time figure out the right DAX syntax to get what I'm looking for.

I have a table visual that is pulling data from a SharePoint list. From this data, I need to calculate the %completed, %Incomplete, %Blank.

I'm pretty sure I have those measures setup correctly.

What I need to do now is get a rolling 12 months based on the %Completed measure.

I've done the whole google search to see what I can find and I do find some entries that should be helping but what I'm not getting is the rolling 12 months. It's just returning the same information that's in my %Completed Column.

I've created a Calendar table as well as my data table and I'm still not getting what I need. Any help to get this figured out would be very much appreciated.

Re: Looking to get a rolling 12 months on a measure column

lbendlin — Fri, 10 May 2024 22:47:02 GMT

What I need to do now is get a rolling 12 months based on the %Completed measure.

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Re: Looking to get a rolling 12 months on a measure column

gypsi96 — Mon, 13 May 2024 14:25:30 GMT

Thank you for your reply but I'm not understanding what you mean. Can you explain it better to me?

"Either materialize it first, or create a separate measure that implements the entire business logic"

Regards

Re: Looking to get a rolling 12 months on a measure column

lbendlin — Mon, 13 May 2024 17:55:43 GMT

for each point on your X axis compile the list of the 12 months, then calculate the measure for each of them, then return the average.

Re: Looking to get a rolling 12 months on a measure column

Anonymous — Mon, 20 May 2024 07:33:46 GMT

Thank you lbendlin for your prompt reply.

Hi gypsi96 ,

May I ask if your problem has been solved. If the above reply was helpful, you may consider marking it as solution. If the problem is not yet solved, please feel free to ask us a question.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Looking to get a rolling 12 months on a measure column

gypsi96 — Mon, 20 May 2024 19:36:26 GMT

I need to calculate on a text column the number completed, incomplete and blank. From that I need to calculate the rolling 12 months on the % complete. When trying to add this need to my data sheet, I get only the totals for the whole sheet not just the row. I've tried the countrow filter formula that I've found online but still it returns the whole data list number not just the row.

I'm unsure at this point how to get the correct calculations I need and where I need to place them to get to this rolling 12 months total I've been asked for based on the %complete measure.

I have been able to successfully get the percentages for Complete, Incomplete and Blank but those again are done as measures on my visual data table.

Re: Looking to get a rolling 12 months on a measure column

lbendlin — Mon, 20 May 2024 19:48:26 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Looking to get a rolling 12 months on a measure column

gypsi96 — Tue, 21 May 2024 14:51:04 GMT

Sample data - NOT up-to-date

I do not know if this will work, my company has shut down the ability to do Share With Anyone. If you can't open the link I will try to find a different way to get you data.

Thank you

Re: Looking to get a rolling 12 months on a measure column

lbendlin — Tue, 21 May 2024 19:55:48 GMT

yeah, sorry, link asks for access details.

Re: Looking to get a rolling 12 months on a measure column

gypsi96 — Tue, 21 May 2024 20:25:37 GMT

It's not allowing me to send a table either, so I have no clue how to get you the data or the file itself.