DAX HELP

[Anonymous]

I want a column for showing day of last month. But dax is showing data in a different way.

CURRDAYPREVMONTH = DATE(YEAR(ACX_Dates[Date]), MONTH(ACX_Dates[Date])-1, DAY(ACX_Dates[Date]))

ACTUAL
REPORTDATE	CURRDAYPREVMONTH
28-03-2017	28-02-2017
29-03-2017	01-03-2017
30-03-2017	02-03-2017
31-03-2017	03-03-2017
EXPECTED
REPORTDATE	CURRDAYPREVMONTH
29-03-2017	28-02-2017
30-03-2017	28-02-2017
31-03-2017	28-02-2017

[amitchandak]

@Anonymous 

Try like

new column = dateadd([REPORTDATE],-1,month)

Or
new column =date(year([REPORTDATE]),month([REPORTDATE])-1,day([REPORTDATE]))

[Anonymous]

Already used this same formula, result is different.

[Greg_Deckler · Accepted solution]

Hacky, but you could always do this:

Column 2 =
IF(DAY([REPORTDATE]) > DAY(EOMONTH([REPORTDATE],-1)),
DATE(YEAR([REPORTDATE]),MONTH([REPORTDATE])-1,DAY(EOMONTH([REPORTDATE],-1))),
DATE(YEAR([REPORTDATE]),MONTH([REPORTDATE])-1,DAY([REPORTDATE]))
)

[Anonymous]

Thanks a lot Greg. It worked for me.

I need one more help

I want to calculate current Fiscal Year End Date (April - March)

Suppose for Today, Fiscal year end date should be 31-03-2021