Forum Discussion
Dax Replace selectedvalue in measure
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=
| Material Number | Batch Number | Endstock |
| FA03 | DS | 0 |
| FA03 | BS | 730 |
| FA07 | DS | 2300 |
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.
4 Replies
- amitchandak
Super User
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)
)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.
v-kkf-msftCommunity Support
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.
