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
Nissa23
Frequent Visitor

Divide with a dynamic denomitanor

Hi,

Here is an example of my table

YearsDatesVar
20182018-05-231
20182018-06-240
20182018-05-251
20192018-07-121
20192018-08-151
20232023-05-310
20232023-06-121

 

I'd like to calculate a ratio between the sum of Var and the total of days. 

The denominator should be the sum of days for the 5 months (May, June, July, August, September) if the Year is <

YEAR(TODAY()) otherwise it should be the number of days from May 1 to Yesterday.
I've started by creating a new column : 
YearToday = YEAR(TODAY()) (to catch the year , here it's 2023)
And using it in another New column : 

 

NbrOfDays = IF('Table'[Years]<'Table'[YearToday],DATEDIFF(DATE('Table'[Years],05,01),DATE('Table'[Years],09,30),DAY),DATEDIFF(DATE('Table'[Years],05,01),MAX('Table'[Dates]),DAY))

 

It's work : I got 152 days for 2018, 2019 and 120 for 2013.

My problem is that I can't use this column to create my Mesure :  divide(sum(var), NbrOfDays)

Can you help me.

Thanks

 

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@Nissa23 although this will work:

 

divide(sum(var), MAX(NbrOfDays))


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

@Nissa23 although this will work:

 

divide(sum(var), MAX(NbrOfDays))


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.

@parry2k Thanks for your help. It's working.

parry2k
Super User
Super User

@Nissa23 can you please provide the clear data? You are making it super complex for no reason. The var value in your original post is just 1 or 0's



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.

parry2k
Super User
Super User

@Nissa23 let me ask you, what is your expected output based on the original data you shared?



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.

@parry2k 

Here is what should my result 

yearRatio(formula)Ratio(%)
2018sum(var)/NbrOfDays =1521,3
2019sum(var)/NbrOfDays=1521,3
2023sum(var)/NbrOfDays=1200,8
parry2k
Super User
Super User

@Nissa23 if you are adding a measure, you cannot use a column, you have to aggregate it, either by SUM/MIN/MAX/AVG etc.



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.

parry2k
Super User
Super User

@Nissa23 you have also aggregate nbrofdays if you adding a measure:

 

divide(sum(var), sum(NbrOfDays))


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.

@parry2k The formula : 

divide(sum(var), sum(NbrOfDays))

should be (NbrOfDays is already computed in the above formule):

divide(sum(var), NbrOfDays)

because NbrOfDays is a column, I'm not able to use it that's why I'm asing for help.

Thanks

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.