cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Many thanks in advance

Lindsay

1 ACCEPTED SOLUTION
Super User

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.

8 REPLIES 8
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.

Frequent Visitor

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

Frequent Visitor

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

Super User

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.

Frequent Visitor

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

Super User

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.

Frequent Visitor

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

Thank you, Thank you, Thank you,

Lindsay

Super User

@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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors