topic Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX in DAX Commands and Tips

Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

imyconco — Tue, 19 Dec 2023 16:13:18 GMT

Hi, I hope you can help me as I have been trying to do this for days and somehow it doesnt work.
I have a data table like the example below (this is just an example), a Calendar table and a Parameter (Reference Plan).
I would like to use 2 slicers, one with the Calendar[Month Name] and the other with the Paramenter[RefNo], so wehn I filter for example Month= February and Reference Plan = February I will get the cumulative sum for those records that are January & February (highlighted in yeloow below).

Data table:

Paramenter:

Calendar:

I have created the folloing metric attemps but none work:

ACTUALS test = VAR selectedmonth = SELECTEDVALUE('Calendar'[Month])

VAR RefPlan = SELECTEDVALUE(REFERENCE_PLAN[RefNo])

Return

CALCULATE([Commitment ACTUALS],

FILTER( ALLSELECTED(financialmonthlystatus), financialmonthlystatus[Month (Period)] <= selectedmonth && financialmonthlystatus[creationperiod] <= RefPlan)

)

This metric only returns when period and creation period are February = 4

Please note [Commitmet Actuals] is a metric sumarizing all amounts and filetring by Group Type = "COMMITMENT" and feature = "ACTUALS".

Please let me know if you need more information,
Many thanks!

Imy

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

lbendlin — Sat, 23 Dec 2023 21:36:19 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

imyconco — Tue, 02 Jan 2024 11:59:33 GMT

https://app.powerbi.com/links/Ei-YubrJ1J?ctid=9a5cacd0-2bef-4dd7-ac5c-7ebe1f54f495&pbi_source=linkShare

I simplify the data and attaching here report, many thanks!

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

lbendlin — Tue, 02 Jan 2024 16:03:04 GMT

You tried to link to your report - that is not recommended. Please prepare sanitized sample data and share that.

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

imyconco — Tue, 02 Jan 2024 17:28:02 GMT

Code	Year (period)	Month (period)	period	Year (creationdate)	Month (creationdate)	creationdate	amount	group	Type	Feature
D/124	2023	2	01/02/2023	2023	3	01/03/2023	301	B	COMMITMENT	ACTUALS
D/126	2023	4	01/04/2023	2023	5	01/05/2023	402	D	COMMITMENT	ACTUALS
D/128	2023	6	01/06/2023	2023	7	01/07/2023	2	A	COMMITMENT	ACTUALS
D/124	2023	1	01/01/2023	2023	3	01/03/2023	5	C	COMMITMENT	ACTUALS
D/126	2023	10	01/10/2023	2023	11	01/11/2023	22	E	COMMITMENT	ACTUALS
D/128	2023	12	01/12/2023	2024	1	01/01/2024	49	B	COMMITMENT	ACTUALS
D/124	2023	2	01/02/2023	2023	3	01/03/2023	300	D	COMMITMENT	ACTUALS
D/126	2023	4	01/04/2023	2023	5	01/05/2023	100	A	COMMITMENT	ACTUALS
D/128	2023	6	01/06/2023	2023	7	01/07/2023	69	C	COMMITMENT	ACTUALS
D/124	2023	2	01/02/2023	2023	2	01/02/2023	32	E	COMMITMENT	ACTUALS
D/126	2023	10	01/10/2023	2023	11	01/11/2023	300	B	COMMITMENT	ACTUALS
D/128	2023	12	01/12/2023	2024	1	01/01/2024	68	D	COMMITMENT	ACTUALS
D/130	2022	2	01/02/2022	2022	3	01/03/2022	87	A	COMMITMENT	ACTUALS
D/123	2022	4	01/04/2022	2022	5	01/05/2022	3	C	COMMITMENT	ACTUALS
D/125	2022	6	01/06/2022	2022	7	01/07/2022	9	E	COMMITMENT	ACTUALS
D/127	2022	8	01/08/2022	2022	9	01/09/2022	26	B	COMMITMENT	ACTUALS
D/123	2022	10	01/10/2022	2022	11	01/11/2022	75	D	COMMITMENT	ACTUALS
D/125	2022	12	01/12/2022	2023	1	01/01/2023	200	A	COMMITMENT	ACTUALS
D/125	2022	2	01/02/2022	2022	3	01/03/2022	50	C	COMMITMENT	ACTUALS
D/124	2022	12	01/12/2022	2022	1	01/01/2023	48	E	COMMITMENT	ACTUALS
D/126	2022	6	01/06/2022	2022	7	01/07/2022	102	B	COMMITMENT	ACTUALS
D/128	2022	8	01/08/2022	2022	10	01/10/2022	242	D	COMMITMENT	ACTUALS
D/124	2022	10	01/10/2022	2022	11	01/11/2022	382	A	COMMITMENT	ACTUALS
D/126	2022	12	01/12/2022	2023	1	01/01/2023	522	C	COMMITMENT	ACTUALS
D/128	2021	9	01/09/2021	2021	10	01/10/2021	662	E	COMMITMENT	ACTUALS
D/124	2021	11	01/11/2021	2021	12	01/12/2021	802	B	COMMITMENT	ACTUALS
D/124	2023	2	01/02/2023	2023	2	01/02/2023	300	D	COMMITMENT	ACTUALS
D/124	2023	1	01/01/2023	2023	2	01/02/2023	15	C	COMMITMENT	ACTUALS
D/124	2023	1	01/01/2023	2023	3	01/03/2023	10	C	COMMITMENT	ACTUALS
D/124	2023	8	01/08/2023	2023	9	01/09/2023	32	E	COMMITMENT	ACTUALS

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

lbendlin — Tue, 02 Jan 2024 19:47:02 GMT

You want a cumulative measure ?

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

imyconco — Wed, 03 Jan 2024 08:08:54 GMT

I need to be able to use a selector for period month (using the calendar table) and a selector from a parameter (RefNo) which is either Feb or Sep. This wold be the SelectedValues.

With this I need to be able to create a metric to summarize the amounts where the Month (period) are <= selected Month (Period) and Month (creationdate)<=selected RefNo.

I tried maany ways but it doesnt work:

ACTUALS_Cut-OffYTD new = VAR selectedmonth = SELECTEDVALUE('Calendar'[Month])

VAR RefPlan = SELECTEDVALUE(REFERENCE_PLAN[RefNo])

Return

calculate([Commitment ACTUALS], financialmonthlystatus[Month (Period)] <= selectedmonth && financialmonthlystatus[Month (Creation)]<= RefPlan)

Indeed is a cummulative metric from a metric.

What am I doing wrong?

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

imyconco — Wed, 03 Jan 2024 13:00:48 GMT

I need to be able to use a selector for period month (using the calendar table) and a selector from a parameter (RefNo) which is either Feb or Sep. This wold be the SelectedValues.

With this I need to be able to create a metric to summarize the amounts where the Month (period) are <= selected Month (Period) and Month (creationdate)<=selected RefNo.

I tried maany ways but it doesnt work:

ACTUALS_Cut-OffYTD new = VAR selectedmonth = SELECTEDVALUE('Calendar'[Month])

VAR RefPlan = SELECTEDVALUE(REFERENCE_PLAN[RefNo])

Return

calculate([Commitment ACTUALS], financialmonthlystatus[Month (Period)] <= selectedmonth && financialmonthlystatus[Month (Creation)]<= RefPlan)

Indeed is a cummulative metric from a metric.

What am I doing wrong?

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

imyconco — Tue, 16 Jan 2024 09:44:53 GMT

Hi, what I need is a cummulative metric compared to 2 dates. These 2 dates come from a selectedvalue on two slicers. I really dont seem to be able to calculate this.

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

lbendlin — Wed, 17 Jan 2024 23:47:18 GMT

Please indicate the expected outcome based on the sample data you provided.

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

imyconco — Thu, 18 Jan 2024 08:02:50 GMT

Hi, here is a simple example, i would like to have a cummulative or runnning sum of the amount (commitment Actuals), where Month (period) is <= to the selected value from the slicer (Month) (in this screenshot is 2); and the Month (creationperios) is <= to the selected value in Reference Plan (in this case also 2). My result on this example should be 347:

Re: Use Selectedvalue (from 2 different slicers) to creating a cumulative metric with DAX

lbendlin — Thu, 18 Jan 2024 20:27:30 GMT

Change your visual to a matrix and put the "Month creation date" as a column