Forum Discussion
DAX HELP
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 |
- 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]))
)
4 Replies
Anonymous
Try like
new column = dateadd([REPORTDATE],-1,month)
Or
new column =date(year([REPORTDATE]),month([REPORTDATE])-1,day([REPORTDATE]))- 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]))
)
