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, ...
Need Help
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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.