Calculated Day Factor in Report Builder

[dashmarley11]

I currently have a Calculated Measure in a PBI Desktop report that I'm trying to replicate in a different report that's in "MS Report Builder".  

ClcFtpRtTd =

VAR CurrentDate = MAX('TimeDeposits'[TapeDate])

VAR DaysInMonth = DAY(EOMONTH(CurrentDate, 0))

VAR DaysInYear = YEARFRAC(DATE(YEAR(CurrentDate), 1, 1), DATE(YEAR(CurrentDate), 12, 31)) * 365

VAR AdjustmentFactor =  DaysInYear / DaysInMonth

RETURN IFERROR((SUM('TimeDeposits'[CLC_Withdrawal]) / SUM('TimeDeposits'[AvgBook])) * AdjustmentFactor, 0)  

How do I go about replicating/translating the calculated measure from PBI Desktop to a MS Report Builder report?

[Anonymous · Accepted solution]

Hi, @dashmarley11 

Report Builder uses VB.NET-based expressions. You'll need to use the functions available in Report Builder to implement similar calculations.

CurrentDate=Max(Fields!TapeDate.Value)

DaysInMonth=DateDiff("d", DateAdd("m", 1, DateSerial(Year(Fields!TapeDate.Value), Month(Fields!TapeDate.Value), 1)), DateSerial(Year(Fields!TapeDate.Value), Month(Fields!TapeDate.Value), 1))

DaysInYear=DateDiff("d", DateSerial(Year(Fields!TapeDate.Value), 1, 1), DateSerial(Year(Fields!TapeDate.Value), 12, 31)) + 1

AdjustmentFactor =DaysInYear / DaysInMonth

The final=IIf(Sum(Fields!AvgBook.Value) = 0, 0, (Sum(Fields!CLC_Withdrawal.Value) / Sum(Fields!AvgBook.Value)) * AdjustmentFactor)

 You can create a calculated field or use these expressions directly in a report item (e.g., text box, table).

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dashmarley11]

Brilliant!  I'm not sure I would've ever figured that out on my own, thank you!