topic Re: Calculated Day Factor in Report Builder in Report Server

Calculated Day Factor in Report Builder

Anonymous — Wed, 11 Dec 2024 17:37:12 GMT

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?

Re: Calculated Day Factor in Report Builder

Anonymous — Thu, 12 Dec 2024 06:52:59 GMT

Hi, Anonymous

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.

Re: Calculated Day Factor in Report Builder

Anonymous — Thu, 12 Dec 2024 16:42:39 GMT

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