Need to view previous set of dates with quantity based on parameter selection from slicer

[krishnakanth240]

Enginetype visitType visitdate    actualqty
T700.           1fr.         1/17/2024. -1
T700.           1fr.         1/13/2024. -1
T700.           1fr.        12/23/2023 -1
T700.           1fr.        11/16/2023 -1
T700.           1fr.        11/11/2023 -1
T700.           1fr.        10/31/2023 -1
T700.           1fr.        10/28/2023 -1
T700.           1fr.         9/23/2023. -1
T700.           1fr.         9/19/2023. -1
T700.           1fr.         9/9/2023. -1

• So here enginetype, visittype columns from Engine gate master table which are into slicers, visit date is from Engine gate master table and actual qty column is from i9_ Usage table, there is a relationship between Engine gate master(unique)table and i9_usage(fact) table based on sales order date column.
  Now in line chart, X axis I will take visit date, and Sales order. Y axis I need to take nextset quantity measure

Also created a numeric range parameter with starting 5 to 20 like 5,10,15, and 20 that is visit range and took into slicer

Now logic is when user selects 5 in visit range it should display the second set of 5 visit dates with actual qty that is from 10/31/2023 measure to be created dynamically

Similarly when user selects 10, it should display next second set of 10 visit dates with actual qty

Tried this measure but it is not working and picking all the dates.Looking for any suggestions

NextVisitsQty =
VAR SelectedCount = SELECTEDVALUE('ParameterTable'[SelectedVisits])
VAR FirstSetMaxRank =
MAXX(
FILTER(
ALLSELECTED('Engine Gate Master'),
'Engine Gate Master'[Eng Type] = SELECTEDVALUE('Engine Gate Master'[Eng Type]) &&
'Engine Gate Master'[Visit Type] = SELECTEDVALUE('Engine Gate Master'[Visit Type]) &&
'i9_usage'[MvT] = 261
),
[VisitRank]
)
VAR SecondSetMaxRank = FirstSetMaxRank - SelectedCount
VAR SecondSetMinRank = SecondSetMaxRank - SelectedCount + 1
VAR SecondSetVisits =
FILTER(
ALL('Engine Gate Master'),
'Engine Gate Master'[Eng Type] = SELECTEDVALUE('Engine Gate Master'[Eng Type]) &&
'Engine Gate Master'[Visit Type] = SELECTEDVALUE('Engine Gate Master'[Visit Type]) &&
'i9_usage'[MvT] = 261 &&
[VisitRank] <= SecondSetMaxRank &&
[VisitRank] >= SecondSetMinRank
)
RETURN
CALCULATE(
SUM('i9_usage'[Actual Qty]),
SecondSetVisits
)

[Anonymous]

Hi @krishnakanth240 

Here are a few suggestions to consider:
1. Verify that the relationship between the 'Engine Gate Master' table and the 'i9_usage' table is correctly set up based on the 'Sales Order Date' column.

2. Make sure that the 'ParameterTable' with the 'SelectedVisits' column is correctly connected to the slicer and that it interacts with the measure as intended.

3. The FILTER function in the 'SecondSetVisits' variable should be using ALLSELECTED instead of ALL if you want the slicer selections to affect the result.

4. The 'i9_usage'[MvT] = 261 condition seems to be filtering on a specific movement type. Ensure that this condition is necessary for the logic you want to implement.

Here's a revised version of your measure considering the above points:

NextVisitsQty =
VAR SelectedCount = SELECTEDVALUE('ParameterTable'[SelectedVisits])
VAR FirstSetMaxRank =
    CALCULATE(
        MAX('Engine Gate Master'[VisitRank]),
        ALLSELECTED('Engine Gate Master')
    )
VAR SecondSetMaxRank = FirstSetMaxRank - SelectedCount
VAR SecondSetMinRank = SecondSetMaxRank - SelectedCount + 1
RETURN
    CALCULATE(
        SUM('i9_usage'[Actual Qty]),
        FILTER(
            ALLSELECTED('Engine Gate Master'),
            'Engine Gate Master'[VisitRank] <= SecondSetMaxRank &&
            'Engine Gate Master'[VisitRank] >= SecondSetMinRank
        ),
        'i9_usage'[MvT] = 261
    )

Best Regards,

Jayleny

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

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.