Display First week & last week dates in separate columns in a table from selected date range

[MachS]

Hi Team,

My requirement is to display first week and last week dates from selected date range(From and To) of date slicer.

Have tried in may ways but always getting complete list of days from selection not desire output.

Please let me know if need any further information.

Note: date range selection may be 2 weeks or above.

want to display like this in Power BI table visual along with some master data

Last week column should display dates from selected fromdate's week days. means from 9th Sep to 15th Sep

Current Week column should display dates from Selected Todate's week days i.e 16th to 22nd Sep.

As informed user may selected 5 weeks or 12 weeks range also. But Last week always calculate dates from Fromdate's week days and Current week should show dates from ToDate's week days.

Thanks inadvance

Sukanya

[MachS]

[MachS]

in my case week consider from Monday to Sunday.

9th Sep 2024 is Monday so first week dates should be 9th to 15th

if my date range is 09-09-2024 to today(06-11-2024), Current week column should show dates from 04-11-2024. Hope it is clear.

Current week dates in Weeknum(calculate(Max(datetable[Date])))

[danextian]

Hi @MachS 

Things to have:

• a separate dates/calendar table with either a week ending Sunday or a week starting Monday columns.
• A disconnected table with a column containing numbers 1  to 7 and another column  containing 7  to 1 and another column with 7 no space values distinct values. This is needed to apply query/row context otherwise you won't be able to breakdown the first and last week date measures into separate dates.
• Measures to show dates based on current row value from the disconnected table.
• Measures to show the numbers (revenue, volume, collection, etc).

Please note that the numbers need to be aggregated by date and other dimensions and can't be itemized like it is in your screenshot. Please see attached sample pbix.

[MachS]

Hi Danextian,

Thanks for your time and solution proposed.

Could you please provide me step by step formula and fields you have created to understand it more clearly.

Thanks

Sukanya

[danextian · Accepted solution]

Hi @MachS 

The details is in the attached pbix. If you're using an M calendar, getting the end or start of week is a little different but more straightforward.

To specify the ending day

[grazitti_sapna]

Hi @MachS,

To achieve this in Power BI, you can try these steps:

Step 1: Create a Date Range Table

Make sure your model includes a calendar or date table linked to your data for easier date calculations. If you don’t have one, create a new date table using DAX:

DateTable = CALENDAR(MIN('YourData'[Date]), MAX('YourData'[Date]))

Step 2: Calculate the First Week and Last Week Dates

You need to create measures or calculated columns that extract the correct dates for "Last Week" and "Current Week" from the selected date range in your slicer.

Extracting the First Week Dates

You can create a calculated table or use a DAX formula to get the dates for the first week:

LastWeekDates =
FILTER(
    DateTable,
    WEEKNUM(DateTable[Date]) = WEEKNUM(MIN('YourData'[Date]))
)

This will give you only the dates from the week containing the MIN date from your slicer selection.

Extracting the Last Week Dates

Similarly, create a measure or table for the dates of the last week:

CurrentWeekDates =
FILTER(
    DateTable,
    WEEKNUM(DateTable[Date]) = WEEKNUM(MAX('YourData'[Date]))
)

This filters the dates for the week containing the MAX date from your slicer selection.

Step 3: Create a Table Visual

1. Use your master data fields as needed for the table.
2. Add the LastWeekDates and CurrentWeekDates fields to your table visual.

Handling Date Range Selections

• Ensure your slicer is set to the date range from which these calculations will work.
• You may need to adjust the date hierarchy in your table visual if you want to display just the week start and end dates.

Additional Adjustments

• If your date selection spans multiple weeks, ensure your logic for filtering applies only to the first and last weeks as specified.
• You can also use additional DAX functions like STARTOFWEEK and ENDOFWEEK if you need to fine-tune which days are displayed.

If I have resolved your question, please consider marking my post as a solution🎉. Thank you!

[MachS]

Hi Grazitti,

Thanks for your quick reply.

Unfortunately this didn't work for me. and unable to find STARTOFWEEK or ENDOFWEEK in DAX 

but can see in Mquery. 

Just one additional information my report is based on DirectQuery.

I will look forward for your suggestion.

Thanks

[danextian]

Hi @MachS 

This is a rather complex scenario. Apparently, you can't be using date dimension columns in your visual as the first and last week dates need to be in the same row. The question now is how would you know which first week's dates aligns with those from the last week? For example,  day 3 for the first week is the 11th but the 20th is the fifth one for the last week  with 16th being the first).