topic Dynamic Flag Column That Changes Rows Depending On Today's Date in DAX Commands and Tips

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

Anonymous — Thu, 10 Aug 2023 07:59:38 GMT

Hi PowerBi Experts,

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

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

Anonymous — Mon, 14 Aug 2023 07:13:44 GMT

Hi Anonymous ,

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.

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

Anonymous — Tue, 15 Aug 2023 13:06:56 GMT

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,