Need help with DAX: Calculating % from latest store visits

[Anonymous]

Hi All

I am working on a Power BI report which requires me to calculate the % of a product quantity at a store for the latest visit dynamically based on the store level. For example, 

For store C and product X, the latest visit is on 25/4 and hence the calculation should be (2/335)*100=0.59%.

I tried writing DAX, but failed miserably in going further ahead. This is what I wrote-

QuantityX% = var numerator=SUM(QuantityX) var denominator=SUM(TotalStoreQuantity)

return CALCULATE(DIVIDE(numerator,denominator), Latest(Date))

The final % should be the %quantity of product X from all stores(only latest visits). Similarly, when a user selects product Y, the same logic repeats.

The data sample is below-

Store	Product	Date	Quantity of X	Total quantity of all items in store
A	X	25/04/2019	2	289
B	X	17/04/2019	1	360
B	X	30/04/2019	1	377
C	X	04/04/2019	2	467
C	X	25/04/2019	2	335
D	X	12/04/2019	1	519
D	X	12/04/2019	1	519
E	X	11/04/2019	1	499
E	X	11/04/2019	1	499
F	X	01/04/2019	1	302
G	X	18/04/2019	1	226
H	X	19/04/2019	2	596
I	X	12/04/2019	2	472
J	X	30/04/2019	2	245
K	X	23/04/2019	1	430
L	X	05/04/2019	2	439
M	X	25/04/2019	3	644
WW	Y	23/07/2019	1	24
S	Y	10/07/2019	3	353
S	Y	24/07/2019	3	369
Q	Y	19/07/2019	4	191
AZ	Y	11/07/2019	4	339
ZZ	Y	16/07/2019	1	459
QD	Y	05/07/2019	1	214
HG	Y	09/07/2019	2	899
RT	Y	24/07/2019	1	337
DS	Y	30/07/2019	2	224
SS	Y	23/07/2019	3	502
MM	Y	05/07/2019	3	407
FF	Y	31/07/2019	1	15
KK	Y	31/07/2019	1	68
EF	Y	08/07/2019	2	241
DF	Y	12/07/2019	2	81
Aw	Y	22/07/2019	1	66

Any help would be highly appreciated!

[Anonymous]

Thank you so much! Works like a charm.

[Anonymous · Accepted solution]

The following calculation should help:

Product % = 
VAR varLastDate =
    CALCULATE ( MAX ( 'Data'[Date] ) )
RETURN
    IF (
        HASONEVALUE ( Data[Product] ) && HASONEVALUE ( 'Data'[Store] ),
        CALCULATE (
            DIVIDE (
                SUM ( Data[Quantity of X] ),
                SUM ( 'Data'[Total quantity of all items in store] )
            ),
            FILTER (
                ALLEXCEPT ( 'Data', 'Data'[Product], 'Data'[Store] ),
                'Data'[Date] = varLastDate
            )
        )
    )

I've also created a sample workbook you can download here: Sample Workbook