Forum Discussion

Gutierrez's avatar
Gutierrez
Frequent Visitor
3 years ago
Solved

DateDiff with Selected Date Value

Hello, hope you all doing good. I'm trying to Calculate several measures (sales total, etc) but having a filter named "Tenure" which have two options, < 60 Days or >= 60 Days, this values needs to consider the DateDiff between the Agent's Production Date and the Date Selected from Calendar Table, i.e. 

 

Those are two tables, Roster and Sales, if I select from Calendar table 05/17/2022, and from Tenure filter >=60 Days it should show Agent A and Agent B with their sales (because the Tenure is based on the DateDiff bewteen the Selected Date (05/17/2022) and the Agent's Production Dates (9/10/2021 and 2/12/2022). If I select the same Date (05/17/2022) but now from Tenure <60 Days, it should show Agent C because the DateDiff is less than 60.

 

Another thing is if I select multiple values from the Calendar Table, it should consider the last one (i.e. if I select from May 1st to May 5th, the datediff should be with May 5th). 

 

I already found the way to show the correct values from my calculate measures if I select wheter < 60 Days, >= 60 Days or both for one single selected Date, the problem is when I select multiple values and it shows me everything without considering the max date selected.

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Gutierrez ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a measure as below to judge if the data is in the period

    Flag = 
VAR _maxdate =
    MAX ( 'Calendar'[Date] )
VAR _seltenure =
    SELECTEDVALUE ( 'Tenure'[Tenure] )
VAR _selagent =
    SELECTEDVALUE ( 'Sales'[AGENT NAME] )
VAR _pdate =
    CALCULATE (
        MAX ( 'Roster'[PRODUCTION DATE] ),
        FILTER ( 'Roster', 'Roster'[AGENT NAME] = _selagent )
    )
RETURN
    IF (
        _seltenure = ">= 60 Days"
            && DATEDIFF ( _pdate, _maxdate, DAY ) >= 60,
        1,
        IF ( _seltenure = "< 60 Days" && DATEDIFF ( _pdate, _maxdate, DAY ) < 60, 1, 0 )
    )

    2. Create a table visual as below screenshot and apply a visal-level filter on it with the condition (Flag is 1)

    If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Gutierrez ,

    I created a sample pbix file(see the attachment), please check if that is what you want.

    1. Create a measure as below to judge if the data is in the period

    Flag = 
VAR _maxdate =
    MAX ( 'Calendar'[Date] )
VAR _seltenure =
    SELECTEDVALUE ( 'Tenure'[Tenure] )
VAR _selagent =
    SELECTEDVALUE ( 'Sales'[AGENT NAME] )
VAR _pdate =
    CALCULATE (
        MAX ( 'Roster'[PRODUCTION DATE] ),
        FILTER ( 'Roster', 'Roster'[AGENT NAME] = _selagent )
    )
RETURN
    IF (
        _seltenure = ">= 60 Days"
            && DATEDIFF ( _pdate, _maxdate, DAY ) >= 60,
        1,
        IF ( _seltenure = "< 60 Days" && DATEDIFF ( _pdate, _maxdate, DAY ) < 60, 1, 0 )
    )

    2. Create a table visual as below screenshot and apply a visal-level filter on it with the condition (Flag is 1)

    If the above one can't help you get the desired result, please provide more sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards