How to get the last 14 days data when second saturday or fourth Saturday is selected

[Pker_Tank]

Hi,

Requirement: Set a Slicer that only shows second and fourth Saturday dates in all months of the year.

When selected, for example 14th Dec (which is the second Saturday of December), the user should be able to see the data from 1st Dec to 14th Dec (14 days). 

I have a table and date column in it.

Please help me find a solution to this requirement.

Thanks heaps in advance.

[Pker_Tank]

Hi, 
TO make it simple, I think the below requirement is enough.
If a user selects a date ending from the date filter (ex: 14 Dec) he should see the data from 1st Dec to 14th Dec.
I have a date table mapped to my table date column.
So the filter will be using the date table, Date column.
In the table, I will be using Date column of my table.
Hope it makes sense. Pleaselet me know if more information needed. Thanks heaps guys

[Anonymous]

Hi @Pker_Tank , hello Kedar_Pande  and 123abc , thank you for your prompt reply!

Based on your description,  check the following measure:

IsInDateRangeMeasure = 
VAR SelectedEndDate = SELECTEDVALUE('Date'[Date],MAX('Date'[Date]))
VAR StartDate = SelectedEndDate - 13  
RETURN
IF(
    MAX('Table'[DateColumn]) >= StartDate && MAX('Table'[DateColumn]) <= SelectedEndDate,
    1,
    0
)

Then filter the table data as shown below:

Best regards,

Joyce

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

[Pker_Tank]

Hi, 
TO make it simple, I think the below requirement is enough.
If a user selects a date ending from the date filter (ex: 14 Dec) he should see the data from 1st Dec to 14th Dec.
I have a date table mapped to my table date column.
So the filter will be using the date table, Date column.
In the table, I will be using Date column of my table.
Hope it makes sense. Pleaselet me know if more information needed. Thanks heaps guys

[Pker_Tank]

Hi,

To make it simpler

If a user selects a end date say 14 Dec in the flter, he should see the data 2 weeks back say 1 dec to 14 dec.
I have a date table mapped to my table date column. 

[Kedar_Pande]

@Pker_Tank 

Create a calculated column

Second_Fourth_Saturday = 
VAR SelectedDate = 'YourTable'[Date]
VAR DayOfWeek = WEEKDAY(SelectedDate, 2) 
VAR DayOfMonth = DAY(SelectedDate)
VAR WeekNumber = WEEKNUM(SelectedDate, 2) 
RETURN 
IF(
(DayOfWeek = 6 && WeekNumber = 2) || (DayOfWeek = 6 && WeekNumber = 4), 
1, 
0
)

In the slicer, use Second_Fourth_Saturday to filter only rows with the value 1 (second or fourth Saturdays).

Create a measure

Data_Upto_SelectedSaturday = 
VAR SelectedSaturday = MAX('YourTable'[Date])
VAR FirstDayOfMonth = DATE(YEAR(SelectedSaturday), MONTH(SelectedSaturday), 1)
RETURN
CALCULATE(
SUM('YourTable'[YourDataColumn]),
'YourTable'[Date] >= FirstDayOfMonth && 'YourTable'[Date] <= SelectedSaturday
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[123abc]

You can follow these steps:

1. Create a Date Table:

   • Ensure you have a Date table in your model. If not, you can create one using DAX:

     DateTable = CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31))

2. Add Columns for Second and Fourth Saturdays:

   • Add calculated columns to identify second and fourth Saturdays:

     SecondSaturday = 
     VAR CurrentMonth = MONTH([Date])
     VAR CurrentYear = YEAR([Date])
     VAR FirstDayOfMonth = DATE(CurrentYear, CurrentMonth, 1)
     VAR FirstSaturday = FirstDayOfMonth + (7 - WEEKDAY(FirstDayOfMonth, 2))
     VAR SecondSaturday = FirstSaturday + 7
     RETURN IF([Date] = SecondSaturday, 1, 0)
     
     FourthSaturday = 
     VAR CurrentMonth = MONTH([Date])
     VAR CurrentYear = YEAR([Date])
     VAR FirstDayOfMonth = DATE(CurrentYear, CurrentMonth, 1)
     VAR FirstSaturday = FirstDayOfMonth + (7 - WEEKDAY(FirstDayOfMonth, 2))
     VAR FourthSaturday = FirstSaturday + 21
     RETURN IF([Date] = FourthSaturday, 1, 0)

3. Create a Slicer:

   • Add a slicer to your report and use the SecondSaturday and FourthSaturday columns to filter the dates.

4. Create a Measure for the Last 14 Days:

   • Create a measure to calculate the last 14 days of data when a second or fourth Saturday is selected:

     Last14DaysData = 
     VAR SelectedDate = MAX('DateTable'[Date])
     RETURN CALCULATE(
         SUM('YourDataTable'[YourDataColumn]),
         FILTER(
             'YourDataTable',
             'YourDataTable'[Date] >= SelectedDate - 13 &&
             'YourDataTable'[Date] <= SelectedDate
         )
     )

5. Visualize the Data:

   • Use the Last14DaysData measure in your visuals to display the data for the last 14 days based on the selected date.

This setup will allow you to select the second or fourth Saturday from the slicer and view the data for the last 14 days up to the selected date.

Let me know if you need any further assistance!

[Pker_Tank]

Hi @123abc  Thanks for your suggestion.

One quick change, In the second Saturday logic, I need the below dates.
May 11, May 25, June 8, June 22, July 6, July 20, Aug 3 ect. it's every 2 weeks.
How can I achieve that? Thanks