Forum Discussion

MojoGene's avatar
MojoGene
Icon for Post Patron rankPost Patron
9 years ago

Divide by YEARFRAC syntax

I can't seem to get the syntax correct on what appears to be a simple measure.   I have a measure that returns the current number of hours that have been billed and paid for the current year, [Hour...
Need Help
MojoGene's avatar
MojoGene
Icon for Post Patron rankPost Patron
9 years ago

Bhavesh:

 

Thanks once again. Unfortunately, it may not be possible for me to create a column for [Hours Paid]. In the underlying data, hours are only relevant to the calculation of the bill. Once hours are relieved (converted from WIP to AR), the hours are no longer important. So, when a payment on a bill is made, the line entries show the amount of the payment recorded against the amount of the bill, but the hours columns for these lines are shown as zero values.

 

In order to come up with a value for Hours Paid, I had to calculate this value by aggregating the data for each billing statement and then taking Sum(BillTable[Hours Billed]) and multiplying this value by a fraction represented by Sum([Fee Amout Paid]) / Sum([Fee Amount Billed]).

 

The measure actually looks like this:

 

Hours Paid = CALCULATE(IF(SUM(BillTable[FeeAmountDue])=SUM(BillTable[FeeBilled]),0,Sum([HoursBilled])*(Sum([FeeAmtPaid])/Sum([FeeBilled]))),FILTER(BillTable,[BillNumber]),Table_BasicCalendarUS[YearKey]=YEAR(TODAY()))

I hope this gives you some idea of why the calculated column does not seem possible in this setting.

MojoGene's avatar
MojoGene
Icon for Post Patron rankPost Patron
9 years ago

Bhavesh:

 

I think I stumbled on the solution. I created the Year Fraction column as you suggested and then divided the measure by the column in this manner:

 

Hours Paid Projected for Year = [Hours Paid]/CALCULATE(MIN(Table_ExtendedCalendar[Year Fraction]),Table_ExtendedCalendar[DateKey]=TODAY())

The results seem to be correct, so I hope there is no hidden flaw that I am overlooking. The actual function used (MIN, MAX, AVE) does not matter since the TODAY filter yields only one number.

 

Thanks again for your efforts.