Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
astrickl
Frequent Visitor

Rolling 12 months total multiple columns with blanks - Resolved

EDIT: I realize I wasn't explaining the problem well enough, and that is my own inexperience with data modeling. But I managed to accomplish what I intended using a measure funtion to calculate the number of yards that had certain comments, 

Yardage w/ no comment = CALCULATE(SUM('Shade Data'[Passed Yardage]),'Shade Data'[Pass w/o comment]="x")

then a rolling 12 months measure based off of that. 

 

 

I'm trying to get a rolling 12 month total yardage for each of the below columns. For example, how many yards passed w/o comment, which with just the data below should show 16,687.3 yds. I can get the rolling 12 months to work to calculate pass/failed yardage, but I can't seem to get a measure to work to only count the yardage with an X in that particular column.  Or do I need to format my data differently to make this measure work?

 

Table: Shade Data

DatePass w/o commentPass with commentPass with limitPass with extreme limitFailPassTotal YardageFailed YardagePassed Yardage
9/10/2020X    X7744.207744.2
9/10/2020X    X8943.108943.1
9/17/2020 XXX X19385.2019385.2
9/24/2020 XXX X8960.708960.7
9/24/2020 XXX X2051.202051.2
9/24/2020 XXX X37545.1037545.1
10/8/2020 XXX X9197.109197.1
10/8/2020 XXX X16973.1016973.1
10/8/2020    X 7387.97387.90
10/29/2020 XX  X26821.3026821.3
11/5/2020 XXX X14484.7014484.7
1/5/2021    X 13789.313789.30

 

 

 

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

Shouldn't the answer be 16,687.3?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Yes, it should. 😅 Thanks for pointing that out, I've edited my post. 

Hi,

I am assuming that your question has been resolved already.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
lbendlin
Super User
Super User

I'm trying to get a rolling 12 month total yardage

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

The below visualizations are a sample of the year to date based off this data. The part I'm having trouble with is getting the rolling 12 month totals of the comment percentages as well as the yardage of each of the comments. 

astrickl_1-1695653011017.png

Sample data 



where do you want to take it from here?

lbendlin_0-1695661439659.png

 

Note there are some small data issues in your sample data.

I was also hoping to get the yardage total for each one, so how many yards in the rolling 12 months were Pass with limit etc. And is there a way to do it with a measure? Or is a slicer the only way?

That data looks a bit weird.

 

lbendlin_0-1695664921168.png

 

The way you formatted the data there shouldn't be any total for Passed Yardage if the result was Fail. Similarly there shouldn't be any result in Sum of Failed Yardage if the result is a Pass of any kind. But the reason it is formatted the way I posted it is because there isn't just one result. On some dates, we get total yardage that has some that passes and some that fails. And that pass can be with limit comments, and extreme limit comments in the same amount of yardage.  

 

 On some dates, we get total yardage that has some that passes and some that fails. And that pass can be with limit comments, and extreme limit comments in the same amount of yardage.  

 

you will need to provide the rules of precedence. or alternative guidance on what should happen.  Ideally you would show the expected result based on your sample data.

 

there shouldn't be any total for Passed Yardage if the result was Fail.

That statement is not supported by your sample data.

lbendlin_0-1695673759096.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.