Calculate Days of Month

[dashmarley11]

I currently have a formula that I'm annualizing by multiplying by 12.  However, to be more accurate I'd like to incorporate a "Days in Month / Days in Year" based off of the "TapeDate" that is in the data so that it's dynamic.

Current formula:

Measure1 = IFERROR((SUM('Deposits'[CLC_Withdrawal]) / SUM('Deposits'[AvgBook]))*12,0)

Expected formula:

Measure2 = IFERROR((SUM('Deposits'[CLC_Withdrawal]) / SUM('Deposits'[AvgBook])) * (Days in Year / Days in Month), 0)

[dashmarley11]

@Luis_Goncalves   Works perfect!  Thank you

[Luis_Goncalves · Accepted solution]

Hello @dashmarley11 

Please try bellow measure and let me know if it worked the way you wanted to! 🙂
If it didn't work please share sample data or extra information.

Measure2 = 
VAR CurrentDate = MAX('Deposits'[TapeDate])
VAR DaysInMonth = DAY(EOMONTH(CurrentDate, 0))
VAR DaysInYear = YEARFRAC(DATE(YEAR(CurrentDate), 1, 1), DATE(YEAR(CurrentDate), 12, 31)) * 365
VAR AdjustmentFactor = DaysInMonth / DaysInYear
RETURN 
    IFERROR((SUM('Deposits'[CLC_Withdrawal]) / SUM('Deposits'[AvgBook])) * AdjustmentFactor * 12, 0)

[PijushRoy]

Hi @dashmarley11 

Please check the below code. Assuming the year starts from 1st Jan and ends on 31st Dec. If not, please update the date in YearStart and YearEnd in the formula.
If not work, please share sample data in Excel format and post the expected result

Measure2 = 
VAR CurrentDate = MAX('Deposits'[TapeDate]) 
VAR DaysInMonth = DAY(EOMONTH(CurrentDate, 0)) 
VAR YearStart = DATE(YEAR(CurrentDate), 1, 1) 
VAR YearEnd = DATE(YEAR(CurrentDate), 12, 31) 
VAR DaysInYear = COUNTROWS(DATES(YearStart, YearEnd)) 

RETURN 
    IFERROR(
        (SUM('Deposits'[CLC_Withdrawal]) / SUM('Deposits'[AvgBook])) * (DaysInYear / DaysInMonth),
        0
    ) 

[dashmarley11]

I'm getting an error notification stating that there aren't enough arguments represented for the DATE function.  It requires 3 (Year, Month, Day)

VAR DaysInYear = COUNTROWS(DATE(YearStart, YearEnd))