Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    6 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.