Forum Discussion
Previous Month Category
Hi,
I have a dataset of about 2 million rows and 20 colunms split across 12 months. Each item appears only once per month and can have the status new, open or closed. I want to keep track of the previous month status of each item. Currently I use measure and a date table
I am not 100% sure if I should have an ALLEXCEPT clause here or not, but it appears to be working fine. There is an issue that occurs when I filter by Status while displaying the previous and current status side by side
I would like the blank rows for current status not to appear; I think it may be to so with the evaluation context of the measure that they are there and I know that these blank rows have the current status of closed for July.
To attempt to workaround this I tried to use the same code as the measure above to create a calculated column, but I got a error about not enough memory.
I have also tried the below, but it does not give me the correct results
I would be grateful if someone could give me a solution for creating a calculated column or way of using a measure that can be filtered by status.
Thanks,
Bernard
[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.
6 Replies
Anonymous , If I got you correctly
Final previous Status
If (isblank([Current status]) , blank(), [previous Status])
I Hope , [previous Status] is correct.
Remember display is a superset of measure. Any other measure can force the display of blank.
Anonymous - Surprised that you got an out of memory error on just 2 million lines of code. Perhaps
Column = VAR __PreviousMonth = EOMONTH([ReportingMonth],-1) VAR __Status = CALCULATE(MAX([Status]),[ReportingMonth]=__PreviousMonth) RETURN __StatusHard to be sure because not 100% on your data. Can you post sample of the critical fields, like ReportingMonth? Do you have a status per day or ? Do you care about having multiple years?
Might be:
Column = VAR __PreviousMonth = MONTH(EOMONTH([ReportingDate],-1) VAR __Status = CALCULATE(MAX([Status]),MONTH([ReportingDate])=__PreviousMonth)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
Thanks for your help Greg_Deckler . I just need to add ALLEXCEPT(Table1,Table1[ID]) and your solution worked.
