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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LindsayK
Frequent Visitor

DAX to calculate only if X-Axis value is less than a value based on the current year

Hello Community,

 

I'm a seasoned Excel user, but new to Power BI and DAX.  For my current project, I have measures such as this one:

 

3rd Year Transfers Graduation (Cumulative) = [Count of 3rd Year Transfers Graduated (Cumulative)]/[Distinct Count of Transfers ID]

 

that return cumulative graduation percentage rates (for 2nd year grad rate, 3rd Year grad rate, 4th year grad rate, 5th year grad rate, 6th year grad rate) for each cohort year on the X-Axis on a column chart (see pic below):

 

The measure works great to provide the cumulative graduation rates for each cohort year on the X-Axis, however, for the individual cohort years on the X-Axis, we don't want to display any graduation rate for the graduation years that haven't occurred yet.  For example, the cohort year 2016 on the X-Axis is showing grad rates for the 3rd year, 4th year, 5th year, and 6th year, but yet we have only just completed grad year 2017 so those grad years haven't actually occurred yet and those data representing them is misleading.  I did some research and tried the following formula, but it didn't work as expected.  Any ideas?

 

 

(Failed Formula) 2nd Year Transfers Graduation (Cumulative) = CALCULATE(DIVIDE([Count of 2nd Year Transfers Graduated (Cumulative)],[Distinct Count of Transfers ID]), 'Transfer_Student_Base_Dataset'[Cohort Year]<=year(TODAY()-1))

Cumulative Grad Rates by Cohort Year.JPG

 

 

 

 

 

 

 

 

 

 

Many thanks in advance Smiley Happy

 

Lindsay

1 ACCEPTED SOLUTION

I don' have sample data but trying to figure out, try this

 

myMeasure = 
var myCalculation = [Count of 2nd Year Graduated (Cumulative)]/[Distinct Count of ID]
return IF(MAX([Cohort Year])<=YEAR(TODAY())-1,myCalculation,BLANK())

I assumed these are already measure in your data model:

 

[Count of 2nd Year Graduated (Cumulative)]
[Distinct Count of ID]


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

8 REPLIES 8
parry2k
Super User
Super User

at first look this looks wrong, seems like you want to go back to previous year:

 

year(TODAY()-1))

 

correct calculation should be

 

year(today())-1



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you Parry, but not quite.  The formula is changing the actual rates to incorrect values and the rates were previously correct.  I just need for the rates to not show if the year isn't applicable.  For one example, if we had a few students from the 2016 transfer student cohort graduate this year (their 2nd year enrolled), then we don't want their 3rd, 4th, 5th, or 6th year graduation rates to populate because that cohort's 3rd, 4th, 5th, and 6th year hasn't occurred yet.  Since we are calculating cumulative rates, technically the cumulative 3rd, 4th, 5th, and 6th year graduation rates would be the same as the 2nd at this point in time, however, since the 3rd, 4th, 5th, and 6th year graduation years haven't occurred yet for the 2016 cohort, in order to avoid confusing the viewer we wouldn't want them represented with the cumulative data.  Maybe I need some kind of filter or if statement instead?  I can't seem to figure it out.

 

Thanks again,

 

Lindsay

 

 

If it were a calculated column in an Excel table, the formula at the row level would look like this:

=IF([Cohort Year]<=YEAR(TODAY())-1,[Count of 2nd Year Graduated (Cumulative)]/[Distinct Count of ID],"")

 

 

 

try this, giving idea based on your excel formula:

 

myMeasure = 
var myCalculation = [Count of 2nd Year Graduated (Cumulative)]/[Distinct Count of ID]
return IF([Cohort Year]<=YEAR(TODAY())-1,myCalculation,BLANKI())

Change the name of my measure to whatever you want to call it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you Parry.  The formula returns an error stating that "A single value for column 'Cohort Year' in table 'Transfer_Student_Base_Dataset cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."  I did some research on this error but I can't determine what might be needed to correct it.  Do you know if it's a simple fix?

 

Thank you,

 

Lindsay

I don' have sample data but trying to figure out, try this

 

myMeasure = 
var myCalculation = [Count of 2nd Year Graduated (Cumulative)]/[Distinct Count of ID]
return IF(MAX([Cohort Year])<=YEAR(TODAY())-1,myCalculation,BLANK())

I assumed these are already measure in your data model:

 

[Count of 2nd Year Graduated (Cumulative)]
[Distinct Count of ID]


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Perfect Parry!  Thank you for being so patient and working through this with me.  This is an enormous help to us Smiley Very Happy

 

Thank you, Thank you, Thank you,

 

Lindsay

@LindsayK glad to hear that I was able to help. that is what community all about. Feel free to reach out anytime. Took bit longer because was working without any sample data. 🙂

 

Cheers,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.