Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ...
Tips and Tricks
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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!

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Yeah, that logic is nasty. Yours however is simpler:

 

Column =
VAR __Table = FILTER('Table',[Date] <= EARLIER([Date]) && [Product]=EARLIER([Product]) && [Market]=EARLIER([Market]))
RETURN
IF(SUMX(__Table,[Value])>0,1,BLANK())
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Greg_Deckler Thanks for sharing the code. It is however not giving the 'Required Column' results for all records. Can you please check once? I was trying a similar thing since long 😞

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      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.