Forum Discussion
How to persist a column over time?
| Product | Market | Date | Value | Required Column |
| P1 | M1 | Nov-19 | 1 | 1 |
| P1 | M1 | Dec-19 | 1 | |
| P1 | M1 | Jan-20 | 1 | |
| P2 | M1 | Oct-19 | ||
| P2 | M1 | Nov-19 | ||
| P2 | M1 | Dec-19 | 1 | 1 |
| P2 | M1 | Jan-20 | 1 | |
| P1 | M2 | Sep-20 | ||
| P1 | M2 | Nov-19 | 1 | 1 |
| P1 | M2 | Jan-20 | 1 | |
| P2 | M2 | Dec-19 | ||
| P3 | M2 | Jan-20 | 1 | 1 |
The Value column is a conditional column. The need is to persist it over time, i.e. for every unique Product-Market combination, as soon as a '1' is encountered in the Value column, all succeeding months should also be 1 for that Product-Market combination.
See attached PBIX. One, you need an actual Date column. Two, I can't tell what your dates are, is Nov-19 11/1/2019 or 11/19/????
Assuming the first, you have an issue in your data where you have September 2020 as not a 1 so maybe it is the other way? Difficult to tell.
Anyway, see attached and let me know.
6 Replies
Perhaps a variation of Cthulhu - https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211
Thanks for replying Greg_Deckler but couldn't exactly replicate the logic in this scenario.
Would it be possible for you to list it out explicitly?
Thanks in advance!
Yeah, that logic is nasty. Yours however is simpler:
Column =VAR __Table = FILTER('Table',[Date] <= EARLIER([Date]) && [Product]=EARLIER([Product]) && [Market]=EARLIER([Market]))RETURNIF(SUMX(__Table,[Value])>0,1,BLANK())
I've just checked the supplied code and it confirms my own (below)
Could it be that your dates are out of order (for instance you have sep 2020 before nov-2019?
Column = var lastDateOne = maxx( filter('Table (2)', 'Table (2)'[Product]=EARLIER('Table (2)'[Product]) && 'Table (2)'[Market]=earlier('Table (2)'[Market]) && 'Table (2)'[Value]=1) ,'Table (2)'[Date]) var showOne = if( and('Table (2)'[Date]>=lastDateOne,not(isblank(lastDateOne))),1,blank()) return showOne