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 valueRETURNCALCULATE( 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 .

Rahp

Helper I

1 year ago

Solved

DAX Issue

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 .

pankajnamekar25
1 year ago
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.

5 Replies

pankajnamekar25
Super User
1 year ago
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.
v-sdhruv
Community Support
1 year ago
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!
v-sdhruv
Community Support
1 year ago
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
Community Support
1 year ago
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
Community Support
1 year ago
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

Forum Discussion

DAX Issue

5 Replies

Related content

DAX Issue

DAX code issue

DAX issue ISBLANK

Basic DAX Issue

Matrix calculation Issue - DAX