Calculating the number of days and by capturing the date from a column based on ID Selection.

[GokulBALAJI]

Hey Guys, we have Table A (ID(Int), Name(String), Start Date(Date)), Table B (No of Days(Int)). We use slicer to select the ID and No of Days, it is maintained within the report but different page. 

Functionality Required:

1. Select ID from [SelectedID] Slicer

2. Select No of days from [No of Days] Slicer

3. Capture the Start Date of selected ID

4. Calculate date range(For example we have selected 4 in the no of days slicer) it should display + and - of the start date based on no of days selection. 

5. In the second page, Assume we have start date of the selected id is (11/14/2024) as per no of days selection it should display from 11/10/2024(-4 days) to 11/18/2024 (+4 days)

6. We tried by using DAX but it didn't work for us. Please find the DAX expression below.

DateRangeFlag =

VAR selectedstartdate =

    CALCULATE(

        MAX('Table A'[Start Date]),

        FILTER('Table A', 'Table A'[ID] = [SelectedID]) 

    )

VAR startdate = selectedstartdate - [SelectedNumberofdays]

VAR enddate = selectedstartdate + [SelectedNumberofdays]

VAR selecteddate = MAX('Table A'[Start Date])  

SelectedID = ID Slicer Name. 

Please let us know the best way to achieve the functionality. Feel free to ask questions/doubts about the requirement. 

RETURN

    IF(selecteddate >= startdate && selecteddate <= enddate, "Yes", "No")

Thanks in advance. 

[v-nuoc-msft]

Hi @GokulBALAJI 

Thank you very much Bibiano_Geraldo and Kedar_Pande  for your prompt reply.

I'm not sure if I'm understanding your thoughts correctly. Perhaps you need to output “Yes” only if the slicer is selected. If so, try the following:

”Table A“

"Table B"

"SelectID Table"

SelectID Table = 
 SELECTCOLUMNS(
    'Table A',
    "SelectID",
    'Table A'[ID]
 )

Create a measure.

DateRangeFlag = 
VAR SelectedID = VALUES('SelectID Table'[SelectID])
VAR SelectedNumberOfDays = SELECTEDVALUE('Table B'[No of Days])
VAR SelectedStartDate = 
CALCULATE(
    MAX('Table A'[Start Date]),
    'Table A'[ID] IN SelectedID
)
VAR DateRangeStart = SelectedStartDate - SelectedNumberOfDays
VAR DateRangeEnd = SelectedStartDate + SelectedNumberOfDays
VAR CurrentDate = MAX('Table A'[Start Date])
RETURN
IF(
    ISFILTERED('SelectID Table'[SelectID])
    &&
    CurrentDate >= DateRangeStart 
    && 
    CurrentDate <= DateRangeEnd, 
    "Yes", 
    "No"
)

Here is the result.

If you have any other questions, please let me know.

Regards,

Nono Chen

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

[GokulBALAJI]

Hi @v-nuoc-msft  Thank you for your effort. But this isn't what i am expecting. my requirement is simple but missing something to achieve. 

1. Select id

2. capture the date from the selected id

3. calculate number of days(+ & -) from the selected number of days in start date. Eg: start date = 05-01-2024.  Date range should be 03-01-2024 to 07-01-2024 if the number of days (2) is selected. 

4. the details table should show the records which falls in the above date range. 

Please let me know if you need any other additional information on the requirement. 

[v-nuoc-msft · Accepted solution]

Hi @GokulBALAJI 

Late reply. Try this:

"SelectID Table"

SelectID Table = 
 SELECTCOLUMNS(
    'Table A',
    "SelectID",
    'Table A'[ID],
    "StartDate",
    'Table A'[Start Date]
 )

The code is modified as follows,

DateRangeFlag = 
VAR SelectedDate = SELECTEDVALUE('SelectID Table'[StartDate])
VAR SelectedNumberOfDays = SELECTEDVALUE('Table B'[No of Days])
VAR DateRangeStart = SelectedDate - SelectedNumberOfDays
VAR DateRangeEnd = SelectedDate + SelectedNumberOfDays
VAR CurrentDate = MAX('Table A'[Start Date])
RETURN
IF(
    ISFILTERED('SelectID Table'[SelectID])
    &&
    CurrentDate >= DateRangeStart 
    && 
    CurrentDate <= DateRangeEnd, 
    1, 
    0
)

In "Filters", filter the measure and select the records where the measure is equal to "1".

Here is the result.

Regards,

Nono Chen

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

[GokulBALAJI]

Hi @v-nuoc-msft  I tried the method you've mentioned above. but i am receiving this error. 

[v-nuoc-msft]

Hi  @GokulBALAJI 

Data type problem, please check the field mentioned in the error and change its data type.To resolve this, you can use the VALUE function to convert the text to a number or the FORMAT function to convert the number to text. For example:

DateRangeFlagObj = IF(VALUE(SELECTEDVALUE([YourMeasure])) = [YourNumberColumn], 1, 0)

Regards,

Nono Chen

[GokulBALAJI]

i try to use the above expression. but i couln't find the measure if i give in the place of [Your Measure]. it shows parameter is not the correct type. 

[v-nuoc-msft]

Hi @GokulBALAJI 

Can you provide a pbix file or dummy data?

[GokulBALAJI]

Hi @v-nuoc-msft , somehow i managed to get the daterange. But the thing is i wanted to know whether measure will work by capturing the values from different table?

1. created Table A with ID and Date. Start date is captured from the ID selected using the below measure(

*GetStartDate = CALCULATE(MAX('Table A'[start_date]),'Table A'[ID]=SELECTEDVALUE('Table A'[ID]))* )

2. Passing this measure to calculate the date range to the Table B(where the actual table is about to use it in the visualization). 

Measures:

1. Startdate = [GetStartDate]-'Number of Days'[Number of Days Value]

2. Enddate = [GetStartDate]+'Number of Days'[Number of Days Value]

3. Daterange Flag = IF(MAX('Table B'[Start Date])>=[Startdate] && MAXMAX('Table B'[Start Date])<=[Enddate],"Y","N"). 

If we are calculating the above measure by duplicating the Table B, it works. If i try to create the measure from Table A and pass it to Table B to create (Startdate, enddate and daterange flag) it is not working. It is showing Y only for one record which the lot number selected in the slicer(not synced with any table to avoid dependency).

Will the same measure work in next page of the report? For me it didn't.

Kinldy let me know if there is any solution for this issue. Keep Helping. Thanks in advance. 

[GokulBALAJI]

Hi @Bibiano_Geraldo and @Kedar_Pande  I dont see much differences in the output. it is giving the 'Yes' value for daterangeflag. But it is showing 'Yes' for all the records. Expected output is to show only for the date range within the number of days selection. if that is happening then we can use daterangeflag as filter and keep the value as 'Yes'. May i know any other method apart from what i have mentioned. Thank you so much for your support and understanding. 

[Bibiano_Geraldo]

Hi @GokulBALAJI ,

Let try a diferent way by the bellow DAX Code's:

First try this dateRangeFlag measure:

DateRangeFlag = 
VAR selectedstartdate = [SelectedStartDate]
VAR days = [SelectedNumberofDays]
VAR startdate = selectedstartdate - days
VAR enddate = selectedstartdate + days
VAR selecteddate = MAXX(
    FILTER('Table A', 'Table A'[ID] = [SelectedID]),
    'Table A'[Start Date]
)

RETURN
    IF(selecteddate >= startdate && selecteddate <= enddate, "Yes", "No")

In this updated DateRangeFlag measure, selecteddate is calculated using MAXX within the context of the selected ID, ensuring it only considers the start date for the selected ID.

Additionally, make sure that your visualizations or tables are filtered by the DateRangeFlag measure to only show records where the flag is “Yes”.

If above measure work, you can stop right here, if not please follow the steps bellow:

Considering that you have SelectedID and SelectedNumberofDays measures, now you can calculate Start and End Dates for the Range by this DAX:

DateRangeStart =
VAR selectedstartdate = 
    CALCULATE(
        MAX('Table A'[Start Date]),
        'Table A'[ID] = [SelectedID]
    )
RETURN
    selectedstartdate - [SelectedNumberofDays]

DateRangeEnd =
VAR selectedstartdate = 
    CALCULATE(
        MAX('Table A'[Start Date]),
        'Table A'[ID] = [SelectedID]
    )
RETURN
    selectedstartdate + [SelectedNumberofDays]

And now create the DateRangeFlag measure to return "Yes" only if a date in the dataset falls within the calculated start and end dates by this DAX:

DateRangeFlag = 
VAR selectedstartdate = [SelectedStartDate]
VAR days = [SelectedNumberofDays]
VAR startdate = selectedstartdate - days
VAR enddate = selectedstartdate + days
RETURN
    IF(
        'Table A'[Start Date] >= startdate && 
        'Table A'[Start Date] <= enddate, 
        "Yes", 
        "No"
    )

Apply the DateRangeFlag as a Visual Level Filter: In your visual on the report page, set the DateRangeFlag field as a filter and only show records where DateRangeFlag equals "Yes."

If this still doesn’t resolve the issue, another approach could be to use calculated columns instead of measures, which might simplify the logic and ensure the correct context is applied.

Let me know if this helps or if you need further assistance!

Thank you!

[GokulBALAJI]

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

This error is populating when i try to create the measure mentioned above. 

[Bibiano_Geraldo]

Hi @GokulBALAJI ,

Which of 3 measures give's your that error?

[GokulBALAJI]

selectedstartdate

[Bibiano_Geraldo]

This measure is from another user, please double check my post and let me know if you are facing any issue, in my solution i dont have this measure.

Thank you

[Kedar_Pande]

Please share a simplified version of your PBIX file (in English) without sensitive data. You can upload it to a public cloud service like OneDrive, Google Drive, or Dropbox and share the link. This will help in understanding your data structure and the issue, allowing for more precise guidance.

[GokulBALAJI]

Hi @Kedar_Pande , i can't share it here. I dont see the option to upload the report in this portal. 

[Kedar_Pande]

@GokulBALAJI 

Create Measures

SelectedStartDate = 
CALCULATE(MAX('Table A'[Start Date]), 'Table A'[ID] = SELECTEDVALUE('Table A'[ID]))

SelectedDays = SELECTEDVALUE('Table B'[No of Days], 0)

StartOfRange = [SelectedStartDate] - [SelectedDays]

EndOfRange = [SelectedStartDate] + [SelectedDays]

DateRangeFlag = 
IF('Table A'[Start Date] >= [StartOfRange] && 'Table A'[Start Date] <= [EndOfRange], "Yes", "No")

Use DateRangeFlag in the visual filter on Page 2 and set it to “Yes” to display records within the specified date range.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Bibiano_Geraldo]

Hi  @GokulBALAJI ,
It looks like you’re on the right track with your DAX formula, but there are a few adjustments needed to achieve the desired functionality. Here’s a step-by-step approach to help you:

Create two measures to capture the selected ID and No of Days from the slicers:

SelectedID = SELECTEDVALUE('Table A'[ID])

 SelectedNumberofDays = SELECTEDVALUE('Table B'[No of Days])

Create a measure to capture the Start Date based on the selected ID:

SelectedStartDate = 
CALCULATE(
    MAX('Table A'[Start Date]),
    FILTER('Table A', 'Table A'[ID] = [SelectedID])
)

Now, define startdate and enddate based on SelectedStartDate and SelectedNumberofDays

DateRangeFlag = 
VAR selectedstartdate = [SelectedStartDate]
VAR days = [SelectedNumberofDays]
VAR startdate = selectedstartdate - days
VAR enddate = selectedstartdate + days
VAR selecteddate = MAX('Table A'[Start Date]) 

RETURN
    IF(selecteddate >= startdate && selecteddate <= enddate, "Yes", "No")

Use the DateRangeFlag measure in a visual (like a table or card) to show which dates fall within the calculated range. This approach should dynamically adjust the date range based on your slicer selections.

I hope this help you, and if so, please consider to mark this reply as solution and give a Kudo.

Thank you!