Forum

What will be the DA...
 
Share:
Notifications
Clear all

What will be the DAX to find the date for last Saturday, if today is Saturday then today's date else last Saturday date


Posts: 92
Admin
Topic starter
(@sql-admin)
Estimable Member
Joined: 4 years ago

Description: "Learn how to find the date of the last Saturday using a simple DAX formula in Power BI. This guide provides a clear example with sample data, ensuring you can easily implement and understand the process. Ideal for Power BI users and data analysts seeking to enhance their DAX skills."

By following this guide, you can accurately calculate the last Saturday's date using DAX in Power BI, improving your data analysis and reporting capabilities.

To create a DAX formula that finds the date of the last Saturday, considering today is Saturday, the DAX can be written as follows:

LastSaturday =
IF (
WEEKDAY ( TODAY(), 2 ) = 6,
TODAY(),
TODAY() - WEEKDAY ( TODAY(), 2 ) + 5
)

Explanation:

  • TODAY(): Returns the current date.
  • WEEKDAY(TODAY(), 2): Returns the day of the week for the current date, with Monday as 1 and Sunday as 7. Therefore, Saturday is 6.
  • IF (WEEKDAY(TODAY(), 2) = 6, TODAY(), TODAY() - WEEKDAY(TODAY(), 2) + 5): This checks if today is Saturday. If true, it returns today's date; if false, it calculates the date of the last Saturday.

Example with Sample Data:

Let's say today is Friday, May 24, 2024.

  • TODAY(): May 24, 2024.
  • WEEKDAY(TODAY(), 2): 5 (because May 24, 2024, is a Friday).
  • TODAY() - WEEKDAY(TODAY(), 2) + 5: This calculates to May 24, 2024 - 5 + 5 = May 19, 2024, which is the previous Saturday.

If today were Saturday, May 25, 2024:

  • TODAY(): May 25, 2024.
  • WEEKDAY(TODAY(), 2): 6 (because May 25, 2024, is a Saturday).
  • The formula would then simply return TODAY(), which is May 25, 2024.
2 Replies
Posts: 92
Admin
Topic starter
(@sql-admin)
Estimable Member
Joined: 4 years ago

How to Find the Last Saturday Date Using DAX in Power BI?

Calculating dates dynamically is a common requirement in Power BI reports. One such scenario is finding the date of the last Saturday based on the current date. In this post, we’ll explore how to write a DAX formula to meet this requirement.

Problem Statement

You need a DAX formula that:

  1. Returns today's date if today is Saturday.

  2. Otherwise, returns the last Saturday's date.

This requirement is especially useful for building weekly dashboards and reporting snapshots aligned with the week ending on Saturdays.


DAX Solution to Find Last Saturday

LastSaturdayDate =
VAR TodayDate = TODAY()
VAR WeekdayNumber = WEEKDAY(TodayDate, 2) -- Week starts from Monday (1)
RETURN
    IF(WeekdayNumber = 6, TodayDate, TodayDate - WeekdayNumber - 1)

Explanation of the Code

  1. TODAY() - Fetches the current system date.

  2. WEEKDAY(TodayDate, 2) - Returns the day number of the week starting from Monday = 1 and Saturday = 6.

  3. IF Condition - Checks whether today is Saturday (6).

  4. Date Adjustment - Subtracts the difference to reach the previous Saturday if today is not Saturday.


Example Output

Current Date Last Saturday Date
2025-01-05 (Sunday) 2024-12-28
2025-01-06 (Monday) 2024-12-28
2025-01-11 (Saturday) 2025-01-11

Related Use Cases

  1. Finding the Previous Friday or Monday:
    To find the last Friday, adjust the weekday number:

    LastFridayDate =
    VAR TodayDate = TODAY()
    VAR WeekdayNumber = WEEKDAY(TodayDate, 2)
    RETURN
        IF(WeekdayNumber = 5, TodayDate, TodayDate - WeekdayNumber - 2)
  2. Calculating the Start of the Current Week (Monday):

    StartOfWeek =
    TODAY() - WEEKDAY(TODAY(), 2) + 1
  3. Finding the End of the Current Week (Sunday):

    EndOfWeek =
    TODAY() + (7 - WEEKDAY(TODAY(), 2))

FAQs

Q: Why use WEEKDAY() with 2 as an argument?
A: Using 2 makes Monday the first day of the week and simplifies calculations, especially for aligning with workweeks.

Q: What happens if today is already Saturday?
A: The formula directly returns today's date without additional calculation.

Q: Can I use this formula in Power BI visuals?
A: Yes, you can use this measure in card visuals or as a filter condition for time-sensitive reports.

 

Conclusion

This DAX formula offers a simple yet effective way to calculate the last Saturday date in Power BI. It can be extended to handle other weekdays or custom date logic based on reporting requirements. Try implementing this in your Power BI dashboard to streamline weekly reporting and scheduling tasks.

For more discussions and queries on SQL, PL/SQL, and Power BI, visit our forum community to engage with other data enthusiasts!

 

Reply
Posts: 92
Admin
Topic starter
(@sql-admin)
Estimable Member
Joined: 4 years ago

How to Find the Last Saturday Date Using DAX in Power BI?

Calculating dates dynamically is a common requirement in Power BI reports. One such scenario is finding the date of the last Saturday based on the current date. In this post, we’ll explore how to write a DAX formula to meet this requirement.

Problem Statement

You need a DAX formula that:

  1. Returns today's date if today is Saturday.

  2. Otherwise, returns the last Saturday's date.

This requirement is especially useful for building weekly dashboards and reporting snapshots aligned with the week ending on Saturdays.


DAX Solution to Find Last Saturday

LastSaturdayDate =
VAR TodayDate = TODAY()
VAR WeekdayNumber = WEEKDAY(TodayDate, 2) -- Week starts from Monday (1)
RETURN
    IF(WeekdayNumber = 6, TodayDate, TodayDate - WeekdayNumber - 1)

Explanation of the Code

  1. TODAY() - Fetches the current system date.

  2. WEEKDAY(TodayDate, 2) - Returns the day number of the week starting from Monday = 1 and Saturday = 6.

  3. IF

  4. Date Adjustment - Subtracts the difference to reach the previous Saturday if today is not Saturday.


Example Output

Current Date Last Saturday Date
2025-01-05 (Sunday) 2024-12-28
2025-01-06 (Monday) 2024-12-28
2025-01-11 (Saturday) 2025-01-11

Related Use Cases

  1. Finding the Previous Friday or Monday:
    To find the last Friday, adjust the weekday number:

    LastFridayDate =
    VAR TodayDate = TODAY()
    VAR WeekdayNumber = WEEKDAY(TodayDate, 2)
    RETURN
        IF(WeekdayNumber = 5, TodayDate, TodayDate - WeekdayNumber - 2)
  2. Calculating the Start of the Current Week (Monday):

    StartOfWeek =
    TODAY() - WEEKDAY(TODAY(), 2) + 1
  3. Finding the End of the Current Week (Sunday):

    EndOfWeek =
    TODAY() + (7 - WEEKDAY(TODAY(), 2))
  4. Finding the First Day of the Current Month:

    FirstDayOfMonth =
    DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
  5. Finding the Last Day of the Current Month:

    LastDayOfMonth =
    EOMONTH(TODAY(), 0)
  6. Calculating the Last Working Day of the Week:

    LastWorkingDay =
    VAR TodayDate = TODAY()
    VAR WeekdayNumber = WEEKDAY(TodayDate, 2)
    RETURN
        IF(WeekdayNumber < 6, TodayDate + (5 - WeekdayNumber), TodayDate - 1)

FAQs

Q: Why use WEEKDAY() with 2 as an argument?
A: Using 2 makes Monday the first day of the week and simplifies calculations, especially for aligning with workweeks.

Q: What happens if today is already Saturday?
A: The formula directly returns today's date without additional calculation.

Q: Can I use this formula in Power BI visuals?
A: Yes, you can use this measure in card visuals or as a filter condition for time-sensitive reports.

Conclusion

This DAX formula offers a simple yet effective way to calculate the last Saturday date in Power BI. It can be extended to handle other weekdays or custom date logic based on reporting requirements. Try implementing this in your Power BI dashboard to streamline weekly reporting and scheduling tasks.

For more discussions and queries on SQL, PL/SQL, and Power BI, visit our forum community to engage with other data enthusiasts!

Reply

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: