Flag column all dates based on two dates

[Anonymous]

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? 

[tamerj1]

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]

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.

[Greg_Deckler]

@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]

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..