Divide with a dynamic denomitanor

[Nissa23]

Hi,

Here is an example of my table

Years	Dates	Var
2018	2018-05-23	1
2018	2018-06-24	0
2018	2018-05-25	1
2019	2018-07-12	1
2019	2018-08-15	1
2023	2023-05-31	0
2023	2023-06-12	1

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

[parry2k · Accepted solution]

@Nissa23 although this will work:

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

[Nissa23]

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

[parry2k]

@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

[parry2k]

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

[Nissa23]

@parry2k 

Here is what should my result 

year	Ratio(formula)	Ratio(%)
2018	sum(var)/NbrOfDays =152	1,3
2019	sum(var)/NbrOfDays=152	1,3
2023	sum(var)/NbrOfDays=120	0,8

[parry2k]

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

[parry2k]

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

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

[Nissa23]

@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