Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Flag column all dates based on two dates

Hi All,

 

In the flag column I would like to see 1 (or true / false) if the date is between the start and enddate. The startdate is based on the Quantity column, if the quantity is larger then 12, the next 12 months needs to be flagged.

 

Who can come up with a solution? 

 

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Try:

    flag column =
  VAR __Date = [Date]
  VAR __Start = MAXX(FILTER('Table',[Quantity]>12 && [Date]<=__Date),[Date_start])
  VAR __End = MAXX(FILTER('Table',[Quantity]>12 && [Date]<=__Date),[Date_end])
RETURN
  IF(__Date <= __End && __Date >= __Start),1,BLANK())
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Greg,

      Thanks for the quick reply. The flags in green are correct, but the red ones are not. Can't figure out why they are flagged..

       

       

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 

    please try

    flag column =
VAR CurrentDate = 'Table'[Date]
VAR CurrentStart = 'Table'[Date_start]
VAR CurrentEnd = 'Table'[Date_end]
VAR StartDate = CurrentDate - 365
VAR TableBefore =
    FILTER ( 'Table', 'Table'[Date] <= CurrentDate && 'Table'[Date] >= StartDate )
VAR MatchingRecords =
    FILTER (
        TableBefore,
        'Table'[Date] >= CurrentStart
            && 'Table'[Date] <= CurrentEnd
    )
VAR Quantity =
    MAXX ( MatchingRecords, 'Table'[Quantity] )
RETURN
    IF (
        CurrentDate >= CurrentStart
            && CurrentDate <= CurrentEnd,
        1,
        IF ( Quantity >= 12, 1 )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi tamerj1 

       

      Only thing missing are the flags I added in red. From 21-7-2022 untill 21-7-2023 every row should get a flag because from the month the quantity > 12 every row needs a flag until the date_end.