If Statements for last week in Direct Query grabbing all years

[PhazeRCIL]

Hello, I am trying to add a column in my date calendar that says Yes if the week is last week compared to TODAY:

IsLastWeek = IF (AND(WEEKNUM(DimDate[Date])= WEEKNUM(TODAY()) - 1, YEAR(DimDate[Date]) = YEAR(TODAY())), "Yes", "No")

The limitations of this code is that if the week overlaps into the previous year, I don't have that data. But if I go without the Year filter, the function grabs ALL last weeks of every year in my data calendar.

I need to have my storage mode set to DirectQuery to have realtime data displays so I can't normally use the FORMAT function to format Today and DimDate[Date] to the week number

Any advice would be great!

[123abc · Accepted solution]

To handle the issue you're encountering with DirectQuery mode and the limitations of the IsLastWeek column, you can adjust your approach to calculate the last week dynamically without relying on the YEAR function. One way to achieve this is by comparing the date ranges directly. Here's how you can do it:

IsLastWeek =
IF (
AND (
DimDate[Date] >= TODAY() - WEEKDAY(TODAY()) - 6,
DimDate[Date] < TODAY() - WEEKDAY(TODAY()) + 1
),
"Yes",
"No"
)

To handle the issue you're encountering with DirectQuery mode and the limitations of the IsLastWeek column, you can adjust your approach to calculate the last week dynamically without relying on the YEAR function. One way to achieve this is by comparing the date ranges directly. Here's how you can do it:

DAXCopy code

IsLastWeek = IF ( AND ( DimDate[Date] >= TODAY() - WEEKDAY(TODAY()) - 6, DimDate[Date] < TODAY() - WEEKDAY(TODAY()) + 1 ), "Yes", "No" )

Explanation:

• WEEKDAY(TODAY()) gives you the numeric representation of the current day of the week (e.g., Sunday is 1, Monday is 2, etc.).
• TODAY() - WEEKDAY(TODAY()) + 1 gives you the first day (Sunday) of the current week.
• TODAY() - WEEKDAY(TODAY()) - 6 gives you the last day (Saturday) of the previous week.

  Therefore, the condition DimDate[Date] >= TODAY() - WEEKDAY(TODAY()) - 6 checks if the date falls after the last day of the previous week, and DimDate[Date] < TODAY() - WEEKDAY(TODAY()) + 1 checks if the date falls before the first day of the current week.

  This approach dynamically calculates the last week without relying on the year. It should work regardless of the year transition. Make sure your date table (DimDate) includes all the necessary dates for your analysis.

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

  In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[PhazeRCIL]

Thanks! I worked just as I wanted!!
How would you go about doing the same thing but for the previous month?

[123abc]

To determine if a date belongs to the previous month, you can compare the month and year of the date with the month and year of the previous month. Here's how you can adjust your formula to achieve this:

IsPreviousMonth =
IF (
AND (
MONTH ( DimDate[Date] ) = MONTH ( TODAY() ) - 1,
YEAR ( DimDate[Date] ) = YEAR ( TODAY() )
)
||
AND (
MONTH ( DimDate[Date] ) = 12,
MONTH ( TODAY() ) = 1,
YEAR ( DimDate[Date] ) = YEAR ( TODAY() ) - 1
),
"Yes",
"No"
)

In this formula:

• The MONTH() function extracts the month number from the date.
• The YEAR() function extracts the year from the date.
• We check if the month of the date is one less than the current month, and if the year matches the current year.
• Additionally, we handle the case where the date is in December and the current month is January of the next year, signifying the last month of the previous year overlapping with the first month of the current year.

This adjusted formula should correctly identify whether a date belongs to the previous month without needing to filter directly by year.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[PhazeRCIL]

When I entered it into DAX, it says that there are too many arguments passed in this section:
||
AND (
MONTH ( DimDate[Date] ) = 12,
MONTH ( TODAY() ) = 1,
YEAR ( DimDate[Date] ) = YEAR ( TODAY() ) - 1
)