Date Function returning wrong Date

[Anonymous]

Hello All, 

Found a strange error in powerbi, where it is not able to convert month & year to a date for month 6 only:

below the dax:

Transaction Date =

var Year = vw_LoadSummaryINTL[Year]

var Month = vw_LoadSummaryINTL[Month]

var day = IF(vw_LoadSummaryINTL[Month] = 2,28,

IF(vw_LoadSummaryINTL[Month]= 9 , 30,

IF(vw_LoadSummaryINTL[Month]= 4 , 30,

IF(vw_LoadSummaryINTL[Month]= 5 , 30,

IF(vw_LoadSummaryINTL[Month]= 11 , 30, 31)))))

RETURN DATE(Year,Month,day)

For some reason, when the vw_LoadSummaryINTL[Month] = 6, the Transaction Date is 01/07/2020.

What am I missing?

Thank in advance!

[amitchandak · Accepted solution]

@Anonymous , Try var day like

var day = Switch(true(),
vw_LoadSummaryINTL[Month] = 2,28,
vw_LoadSummaryINTL[Month] in (4,6,9,11) , 30,
31)

or

Transaction Date =

 eomonth(date(vw_LoadSummaryINTL[Year], vw_LoadSummaryINTL[Month],1),0)

[Anonymous · Accepted solution]

@Anonymous 

There are small mistakes,  there is only 30 days in June. So change 5 to 6 in your formula:

IF(vw_LoadSummaryINTL[Month]= 6 , 30,

Or you may just use the simple version:

Transaction Date =

var day_= IF(vw_LoadSummaryINTL[Month] = 2,28,

IF(vw_LoadSummaryINTL[Month] in {4,6,9,11}, 30, 31))

RETURN DATE([Year],[Month],day_)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yes, sorry for the error, found that after posting here.

Regardless, I used your simple version and helped me a lot.

thank you for your help!

[amitchandak · Accepted solution]

@Anonymous , Try var day like

var day = Switch(true(),
vw_LoadSummaryINTL[Month] = 2,28,
vw_LoadSummaryINTL[Month] in (4,6,9,11) , 30,
31)

or

Transaction Date =

 eomonth(date(vw_LoadSummaryINTL[Year], vw_LoadSummaryINTL[Month],1),0)