formula in excel make it in PowerBi

[CJKPowerBI]

Hello all,

i have the below formula in excel which basically checks today's date and if before this month 23rd day then it puts the 15th of this month, if todays date is more than 23rd of this month then it puts the 15th of the next month.

=IF(OR((($C2)="Option1"),(($C2)="Option2")),IF(DAY($E2

<=23,DATE(YEAR($E2),MONTH($E2)+1,15),EOMONTH($E2,1)+15),$E2)

how can i transform it to be used in a column or measure in Power BI?

thank you in advance

[tamerj1]

Hi @CJKPowerBI 

Same formula should work just fine for a calculated column, just replace the cell references with column references. 

[lukiz84]

In a calculated column the syntax is the same, you just have to change 2 things:

$C2 must be the column in your table 

$E2 must be another column in your table (where you have the data)

[CJKPowerBI]

My formula gives me an error after those modifications, can you have alook and maybe paste the formula for PowerBi here pls?

[tamerj1]

@CJKPowerBI 

Please paste your formula here in order to be able to amend. 

[CJKPowerBI]

Below is the formula:

Amount to be paid on = IF(OR(([Charge]="Text 1"),([Charge]="Text 2")),IF(DAY([Transcation Date]<=23,DATE(YEAR([Transcation Date],MONTH([Transcation Date])+1,15),EOMONTH([Transcation Date],1)+15),[Transcation Date])

[tamerj1 · Accepted solution]

@CJKPowerBI 

Please try

Amount to be paid on =
IF (
    OR ( [Charge] = "Text 1", [Charge] = "Text 2" ),
    IF (
        DAY ( [Transcation Date] ) <= 23,
        DATE ( YEAR ( [Transcation Date] ), MONTH ( [Transcation Date] ) + 1, 15 ),
        EOMONTH ( [Transcation Date], 1 ) + 15
    ),
    [Transcation Date]
)

[CJKPowerBI]

thank you @tamerj1  that worked great...