Dax Replace selectedvalue in measure

[Anonymous]

Hi All,

In the below given data for paticular material if Calender month and Posting Calender month are equal then Result A or else Result B.

Material Number	Batch Number	CALENDAR_MONTH	CALENDAR_YEAR	Posting_CALENDAR_MONTH	Posting_CALENDAR_YEAR	Endstock
FA03		1	2022	1	2022
FA03		1	2022	1	2022
FA03	DS	1	2022	1	2022	0
FA03	DS	12	2021	1	2022	500
FA03	BS	1	2022	1	2022	730
FA03	BS	12	2021	1	2022	730
FA07	DS	9	2021	1	2022	2300

Below is the dax measure am using and it is direct query.Only Posting_CALENDAR_MONTH is being used in slicer.

Measure=

var selCD = SELECTEDVALUE(DP_ZIC_C06[Posting_CALENDAR_MONTH])

RETURN

IF(Selectedvalue(DP_ZIC_C06[CALENDAR_MONTH])=SelCD,CALCULATE (

SUM(

DP_ZIC_C06[END_STOCK]

)

),CALCULATE (

SUM (

DP_ZIC_C06[END_STOCK]

)

),

SELECTEDVALUE ( DP_ZIC_C06[Posting_CALENDAR_MONTH] ) <> DP_ZIC_C06[CALENDAR_MONTH]

))

expected o/p

Material Number	Batch Number	Endstock
FA03	DS	0
FA03	BS	730
FA07	DS	2300

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

Please try the following formula:

Measure = 
VAR selCD =
    SELECTEDVALUE ( DP_ZIC_C06[Posting_CALENDAR_MONTH] )
VAR tab =
    FILTER ( DP_ZIC_C06, DP_ZIC_C06[CALENDAR_MONTH] = SelCD )
RETURN
    IF (
        COUNTROWS ( tab ) > 0,
        CALCULATE ( SUM ( DP_ZIC_C06[END_STOCK] ), tab ),
        SUM ( DP_ZIC_C06[END_STOCK] )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks a lot Winniz.Solution Worked just as expected 🙂

[amitchandak]

@Anonymous , Try

Measure=
var selCD = SELECTEDVALUE(DP_ZIC_C06[Posting_CALENDAR_MONTH])
RETURN
IF(max(DP_ZIC_C06[CALENDAR_MONTH])=SelCD,CALCULATE (
SUM(
DP_ZIC_C06[END_STOCK]
)
),CALCULATE (
SUM (
DP_ZIC_C06[END_STOCK]
), filter( DP_ZIC_C0, DP_ZIC_C06[CALENDAR_MONTH] <> _selCD)
)
)

or

Measure=
var selCD = SELECTEDVALUE(DP_ZIC_C06[Posting_CALENDAR_MONTH])
RETURN
calculate( SUM(
DP_ZIC_C06[END_STOCK] ), filter( DP_ZIC_C0, DP_ZIC_C06[CALENDAR_MONTH] = _selCD) ) + CALCULATE (
SUM (
DP_ZIC_C06[END_STOCK]
), , filter( DP_ZIC_C0, DP_ZIC_C06[CALENDAR_MONTH] <> _selCD)
)

[Anonymous]

@amitchandak  Solution is not giving me expected result.Please see the edited post.

In Measure for Material FA03,Batchnumber DS value should be 0 as Calender month and CCFOT calender date are same

Thanks for the response.Much helpful.