topic Re: Cumulative YTD with Multiple Criteria in DAX Commands and Tips

Cumulative YTD with Multiple Criteria

Anonymous — Fri, 15 May 2020 15:30:05 GMT

Hi Power BI folks

Looking for some help with the DAX measure named YTD that will be used for table visualization. YTD must show a cumulative sum based on purchase volume to customers. I partially created the DAX which needs some further tweaking.

Cumulative YTD Actual Volume = calculate (SUM(Actual[SALESQYT]*10), DATESYTD('Date'[Date])), but I need to include one more criteria in this DAX measure i.e. for customer A and C the Sales Volume to be multiplied by 20. Sample of the desired data is shown the following table.

DATASETS

Customer	Quantity	Date	Mon_Num
A	86	10/01/2020	1
B	64	01/01/2020	1
D	61	23/02/2019	2
A	51	14/02/2019	2
A	61	05/02/2019	2
D	66	26/03/2019	3
B	91	17/01/2019	1
B	64	08/01/2019	1
C	63	30/04/2020	4
C	94	21/04/2020	4
D	74	12/04/2020	4
C	43	03/04/2020	4
A	73	24/03/2020	3
B	43	15/01/2020	1

FINAL OUTPUTS

Customer	Sum of Quantity	Criteria for DAX
A	5420	multiplied by 20
B	2620
C	4000	multiplied by 20
D	2010

Can anyone help me in completing the DAX the measure to get the below-desired outputs

As shown in the sample data set and the final output.

Re: Cumulative YTD with Multiple Criteria

Anonymous — Fri, 15 May 2020 16:41:43 GMT

Hi Anonymous ,

You can try these measures. Not sure why YTD.

Sum of Quantity = SUM(Table9[Quantity])

Multiplied Factor = IF(SELECTEDVALUE(Table9[Customer ]) IN ({"A","C"}) , [Sum of Quantity] * 20 , [Sum of Quantity]*10)

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!

Re: Cumulative YTD with Multiple Criteria

nandukrishnavs — Sat, 16 May 2020 08:41:14 GMT

Anonymous

Cumulative YTD Actual Volume = CALCULATE ( SUMX ( MyTable, IF ( MyTable[Customer ] IN { "A", "C" }, MyTable[Quantity] * 20, MyTable[Quantity] * 10 ) ), FILTER ( ALL ( 'MyTable'[Date].[Date] ), 'MyTable'[Date].[Date] <= MAX ( 'MyTable'[Date].[Date] ) ) ) //If you are not using the Date Hierarchy then you can remove .[Date] from the above part ('MyTable'[Date].[Date])

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Re: Cumulative YTD with Multiple Criteria

Anonymous — Sat, 16 May 2020 23:23:13 GMT

Thanks Anonymous for your contribution - The DAX measure output was to calculate Cumulative Sum.

Re: Cumulative YTD with Multiple Criteria

Anonymous — Sat, 16 May 2020 23:33:54 GMT

Thanks nandukrishnavs for your inputs. I managed to get the solution fixed, sorry for nicking your DAX a bit and merged with my earlier measure it is perfectly working now. The final DAX measure -

**bleep** YTD Act Vol =

CALCULATE (

SUMX (

Customer,

IF (

Customer[CLIENT]

IN { "A", "C" },

[Act Vol] * 20,

[Act Vol] * 10

)

DATESYTD('Date'[Date]

)

thanks for reaching out to me.

Re: Cumulative YTD with Multiple Criteria

Anonymous — Mon, 18 May 2020 00:08:32 GMT

Please find a file attached. I have set up a model that can accommodate any multiplication factors for each and every customer.

Best