Cumulative YTD with Multiple Criteria

[Anonymous]

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.

[Anonymous]

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

Best

D

[nandukrishnavs]

@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 🙂

[Anonymous]

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.

[harshnathani]

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!!

[Anonymous]

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