DAX Issue

[Rahp]

Options Entered Lots (Open as of Date) =

VAR SelectedStrike = SELECTEDVALUE('Final MO MCX Options'[Strike])

VAR SelectedExpiry = SELECTEDVALUE('Final MO MCX Options'[Expiry Date])

VAR SelectedType = SELECTEDVALUE('Final MO MCX Options'[Type])

VAR SelectedLongShort = SELECTEDVALUE('Final MO MCX Options'[Long / Short])

VAR SelectedDate = MAX('DateTable'[Date])  -- Date slicer value

RETURN

CALCULATE(

    SUM('Final MO MCX Options'[Lots]),

    FILTER(

        ALL('Final MO MCX Options'),

        'Final MO MCX Options'[Strike] = SelectedStrike &&

        'Final MO MCX Options'[Expiry Date] = SelectedExpiry &&

        'Final MO MCX Options'[Type] = SelectedType &&

        'Final MO MCX Options'[Long / Short] = SelectedLongShort &&

        'Final MO MCX Options'[Entry Date] <= SelectedDate &&

        ISBLANK('Final MO MCX Options'[Exit Date])

    )

)
Facing issue with this dax. The end results I want is whichever date I select whether single or in between ranges from the date slicer, the holding lots should be reflected till date. for example if on 21/05/2025, there is no data of lots entered or exited but till that date , that period how many lots I am holding . Its not necessary that that particular date have data. it might not have so if the last entry is of 19/05/2025, then too the holding position as on21/05/2025 should reflect that data .

[v-sdhruv]

Hi @Rahp ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Rahp ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Rahp ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @Rahp ,
Try using-

Options Entered Lots (Open as of Date) =
VAR SelectedStrike = SELECTEDVALUE('Final MO MCX Options'[Strike])
VAR SelectedExpiry = SELECTEDVALUE('Final MO MCX Options'[Expiry Date])
VAR SelectedType = SELECTEDVALUE('Final MO MCX Options'[Type])
VAR SelectedLongShort = SELECTEDVALUE('Final MO MCX Options'[Long / Short])
VAR SelectedDate = MAX('DateTable'[Date]) -- latest date selected in the slicer

RETURN
CALCULATE(
SUM('Final MO MCX Options'[Lots]),
FILTER(
ALL('Final MO MCX Options'),
'Final MO MCX Options'[Strike] = SelectedStrike &&
'Final MO MCX Options'[Expiry Date] = SelectedExpiry &&
'Final MO MCX Options'[Type] = SelectedType &&
'Final MO MCX Options'[Long / Short] = SelectedLongShort &&
'Final MO MCX Options'[Entry Date] <= SelectedDate &&
(
ISBLANK('Final MO MCX Options'[Exit Date]) ||
'Final MO MCX Options'[Exit Date] > SelectedDate
)
)
)
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[pankajnamekar25 · Accepted solution]

Hello @Rahp 

try this measure

Options Entered Lots (Open as of Date) =

VAR SelectedStrike = SELECTEDVALUE('Final MO MCX Options'[Strike])

VAR SelectedExpiry = SELECTEDVALUE('Final MO MCX Options'[Expiry Date])

VAR SelectedType = SELECTEDVALUE('Final MO MCX Options'[Type])

VAR SelectedLongShort = SELECTEDVALUE('Final MO MCX Options'[Long / Short])

VAR SelectedDate = MAX('DateTable'[Date]) -- will work even with ranges

RETURN

CALCULATE(

    SUM('Final MO MCX Options'[Lots]),

    FILTER(

        ALL('Final MO MCX Options'),

        'Final MO MCX Options'[Strike] = SelectedStrike &&

        'Final MO MCX Options'[Expiry Date] = SelectedExpiry &&

        'Final MO MCX Options'[Type] = SelectedType &&

        'Final MO MCX Options'[Long / Short] = SelectedLongShort &&

        'Final MO MCX Options'[Entry Date] <= SelectedDate &&

        (

            ISBLANK('Final MO MCX Options'[Exit Date]) ||

            'Final MO MCX Options'[Exit Date] > SelectedDate

        )

    )

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.