topic Re: If Statements for last week in Direct Query grabbing all years in DAX Commands and Tips

If Statements for last week in Direct Query grabbing all years

PhazeRCIL — Wed, 31 Jan 2024 13:48:30 GMT

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!

Re: If Statements for last week in Direct Query grabbing all years

123abc — Wed, 31 Jan 2024 14:06:13 GMT

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"
)

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.

Re: If Statements for last week in Direct Query grabbing all years

PhazeRCIL — Wed, 31 Jan 2024 14:23:10 GMT

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

Re: If Statements for last week in Direct Query grabbing all years

123abc — Wed, 31 Jan 2024 14:30:02 GMT

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.

Re: If Statements for last week in Direct Query grabbing all years

PhazeRCIL — Wed, 31 Jan 2024 14:46:22 GMT

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
)