Dynamic Flag Column That Changes Rows Depending On Today's Date

[wjlee94]

Hi PowerBi Experts,

I am facing an issue where I need to display a table of data depending on which day of the week it is.

My dataset sample is as below -

           Project Name |  Transaction Month | Reporting Date | Transaction Week Start | Transaction Week End | Weekly Sales
Row 1  Project A        |   August 2023          | 7 August 2023  | 31 July 2023                  | 6 August 2023             | 20
Row 2  Project B        |   August 2023          | 7 August 2023  | 31 July 2023                  | 6 August 2023             | 3
Row 3  Project C        |   August 2023          | 7 August 2023  | 31 July 2023                  | 6 August 2023             | 12
Row 4  Project A        |   August 2023          | 14 August 2023| 7 August 2023              | 13 August 2023           | 14
Row 5  Project B        |   August 2023          | 14 August 2023| 7 August 2023              | 13 August 2023           | 5
Row 6  Project C        |   August 2023          | 14 August 2023| 7 August 2023              | 13 August 2023           | 7
Row 7  Project A        |   August 2023          | 21 August 2023| 14 August 2023             | 20 August 2023          | 9
Row 8  Project B        |   August 2023          | 21 August 2023| 14 August 2023             | 20 August 2023          | 0
Row 9  Project C        |   August 2023          | 21 August 2023| 14 August 2023             | 20 August 2023          | 3

Now, the requirement is that if the user views the report on 14th August 2023 (Monday), the table should only show Rows 1 to 3, to show data within the Transaction Week range. This applies to all subsequent Mondays.

From 15th August 2023 (Tuesday) to 20th August (Sunday), the user should only be able to view Rows 4 to 6. This is how the flag should work.

What I've tried -
I came up with this DAX formula, but it does not work as intended. It should only apply the flag to Rows 4-6, based on today's date (10 Aug), but it applies the flag to Rows 1-3 as well.

VAR TodayDate = TODAY()
VAR MondayTwoWeeksAgo = TodayDate - WEEKDAY(TodayDate, 2) - 13
VAR MondayOneWeekAgo = TodayDate - WEEKDAY(TodayDate, 2) - 6
RETURN
    IF(
        [Transaction Week Start] >= MondayTwoWeeksAgo &&
        [Transaction Week Start] <= MondayOneWeekAgo,
        "Flag",
        BLANK()
    )

I am at a lost, and have been cracking my head for hours now, how can I achieve the aboe - any help will be greatly appreciated!

Thanks & Best Regards,
LeeWJ

[Anonymous]

Hi  @wjlee94 ,

It is not clear what you mean, according to the rules said:

Today is 2023.8.14, which shows the time interval between 2023.7.31 – 2023.8.6, and shows Rows 1 to 3

If it is 2023.8.10, the display time interval is between 2023.7.27 – 2023.8.2, then the display should also be Rows 1 to 3.

Can you show the expected results in the form of pictures, we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wjlee94]

Hi @Anonymous ,

Thanks for the reply. Apologies for not being clear enough.

Please refer to the calendar image below.

Basically, if the user views the report on 14th August (In Red), the report will display the rows for 31 Jul to 6 Aug (corresponding red box).

If the user views the report on 15th August (green box), the report will display the rows for 7 Aug to 13 Aug (corresponding green box). Then if the user views on 16th, 17th, 18th, 19th - it will still display the same rows in the corresponding green box.

Then, if the user views the report in the following week on 21 August (yellow box), the report will still display the rows for 7 Aug to 13 Aug (corresponding yellow box)...and so on.

So based on the above logic, I am expecting a column to apply a flag to the rows depending on the system today's date.

The Transaction Weeks are fixed so that it always starts on a Monday and ends on a Sunday, and if the user views the report between Tuesday to Sunday, it is fixed to show last Monday's to Sunday's data.

If the user views the report on a Monday, then it is fixed to show Monday's to Sunday's data two weeks ago.

Thank you.

Regards,

WJ