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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MojoGene
Post Patron
Post Patron

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, [Hours Paid]. I want to use that number to determine the projected number of hours paid for the current year. Mathematically, this is [Hours Paid] divided by YEARFRAC.

 

Here is the measure that is returning incorrect results:

 

Hours Paid Projected for Year = DIVIDE([Hours Paid],YEARFRAC(STARTOFYEAR(Table_ExtendedCalendar[DateKey]), TODAY(),3))

When I divide [Hours Paid] by 1, I get the correct number of paid hours, so it looks like I am not stating the denominator correctly.

 

Thanks for any help.

5 REPLIES 5
BhaveshPatel
Community Champion
Community Champion

The syntax written for your measure is not a valid syntax. You are trying to divide the measure by the column values.

 

For such types of calculations, you need to use SUMX function.

 

Create YEARFRAC Column in your table first.

 

 

Yearfrac cal= YEARFRAC(STARTOFYEAR(Table_ExtendedCalendar[DateKey],TODAY(),3))

 

Hours Paid Projected for year:=SUMX('TABLE NAME', Table Name[hours column]/Table Name[Yearfrac cal])

 

Note:

1. In SUMX calculation, columns are used for division and not measures.

2. Make sure, you have correct data types set up for your columns.

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Bhavesh:

 

Thanks for your reply. When I try that, I get the result "NaN" (not a number).

 

Any further advice on what i might be doing wrong?

Hi MojoGene,

 

Please follow the below screenshots for the solution. It is tested and working fine.

 

Create Calculated column for your hours column . if you already have one, you do not need to create one.Create Calculated column for your hours column . if you already have one, you do not need to create one.Create another calculated column for yearfrac calculationCreate another calculated column for yearfrac calculationYou can see the results in the screenshotYou can see the results in the screenshotCreate your desired measure for your final results.Create your desired measure for your final results.

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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