Forum Discussion
Previous Month Category
- Anonymous6 years ago
[Status PM] = // calc column var __id = T[ID] var __month = T[Month] // must be datetime RETURN // This will return the status // on the date preceding the // current date. If there are no // temporal gaps in your data, // you'll get what you need. MAXX( TOPN(1, filter( T, T[ID] = __id && T[Month] < __month ), T[Month], DESC ), T[Status] )One piece of good advice: NEVER use CALCULATE in calculated columns in a fact table. If the fact table is even moderately big, you'll very likely get an out-of-memory error. And also, please note that such calculations should be performed in Power Query (where they'll be fast and efficient), not in DAX. Calculated columns should be calculated in DAX only when there's absolutely no other way. One reason for this is that such columns don't get compressed optimally if calculated in DAX, hence reducing the speed of calculations.
Thank you for your reply Greg_Deckler . The solutions have not worked as yet. I think it may down to the data type of the report month.
EOMONTH([ReportMonth],-1) looks to give the right value but then but then the calculate statement returns empty. If I make a column of EOMONTH([ReportMonth],-1) and drag it to a table with Report Month they appear differently formatted.
ReportMonth just contains the date of the last day of the month over time. There can be the same month over different years (july 2019 and July 2020 for example) so year does matter. I have gave a small sample of the key fields below hopefully this will be sufficient?
IDStatusReport Month
| 95294 | Closed | 29/02/2020 |
| 95294 | Closed | 31/01/2020 |
| 95294 | Closed | 31/12/2019 |
| 95294 | Open | 30/11/2019 |
| 95294 | Closed | 31/10/2019 |
| 95295 | Closed | 29/02/2020 |
| 95295 | New | 31/01/2020 |
| 95295 | Open | 31/12/2019 |
| 95377 | Closed | 29/02/2020 |
| 95377 | New | 31/01/2020 |
| 95377 | Open | 31/12/2019 |
| 95377 | New | 30/11/2019 |
Thanks,
Bernard
AnonymousThank you for the solution and advice.
Bernard