How to create a Last 4 Week flag for calendar table that works across multiple fiscal years

[bryanwilson]

I need to show how a few metrics (forecast accuracy, etc...) performed Fiscal Year to Date, as well as in the L4W (Last Four Weeks).

My current solution is to have separate sets of measures to accomplish this.

First set is regular measures and I filter their visuals to Fiscal Year to Date, a column I have built into my calendar table written in M. Great! The second set though, I don't know how to create a similar flag that flags the date in the calendar table if it falls in the last four weeks (from current date) of any given fiscal year. I have a separate set of measures designated L4W, which uses DATEADD to create a table of the last 28 days.

For example, Last Four Weeks from today() is today and the preceding 28 days. So the calendar dates March 19th thru and including April 16th. I want to create a flag column in my calendar table that denotes 3/19 thru 4/16 of all Fiscal Years, so that I can reuse the same measures and put a "L4W" filter on the visuals to accomplish my goal. Any thoughts on how best to do this?

Edit: I figured out the flag to designate which rows of the calendar table are in the last four weeks from a specified date (specified date is called "Complete POS Date Literal")

Flag_L4W = 
    VAR POSValidToDate = 'Calendar'[Complete POS Date Literal]
    VAR POSValidFromDate = 'Calendar'[Complete POS Date Literal]-27
    VAR POSL4WValid = IF(AND('Calendar'[Date]<=POSValidToDate,'Calendar'[Date]>=POSValidFromDate),TRUE,FALSE)
    RETURN
    POSL4WValid

This achieves a TRUE when the row was in the last four weeks from the Complete POS Date Literal date.

How do I extend this to apply to the same range of calendar days but from previous years as well?

[bryanwilson · Accepted solution]

Happened upon a solution that works for me:

Flag_L4W =

VAR POSValidToDate = 'Calendar'[Complete POS Date Literal]

VAR POSValidFromDate = 'Calendar'[Complete POS Date Literal] - 27

VAR StartMonthDay = FORMAT(POSValidFromDate, "MM-DD")

VAR EndMonthDay = FORMAT(POSValidToDate, "MM-DD")

VAR CurrentMonthDay = FORMAT('Calendar'[Date], "MM-DD")

VAR POSL4WValid =

IF(

AND(

CurrentMonthDay >= StartMonthDay,

CurrentMonthDay <= EndMonthDay

),

TRUE,

FALSE

)

RETURN

POSL4WValid