Dynamic Weekly Run Rate Measure based on 2 slicers (Quarter and Month), and different criteria

[dsl98]

Hi, I have searched online for a while but no avail. Assuming I have 2 slicers (slicer 1 has Q1 Q2 Q3 and Q4, and slicer 2 has Months - January, February...) from file 1, that has one to many relationship linked to my source sales file (file 2). How do I make a dynamic measure formula so that everytime I select the slicer it will auto calculate cumulatively, with below criterias:

January	4
February	4
March	5
April	4
May	4
Jun	5
Jul	4
Aug	4
Sep	5
Oct	4
Nov	4
Dec	5
Q1	13
Q2	26
Q3	39
Q4	52
H1	26
H2	52
Jan YTD	4
Feb YTD	8
Mar YTD	13
Apr YTD	17
May YTD	21
Jun YTD	26
Jul YTD	30
Aug YTD	34
Sep YTD	39
Oct YTD	43
Nov YTD	47
Dec YTD (default)	52

basically the expected formula is
WRR = Divide (numerator is filter the sales list, denominator is selected period AND OR quarter as per above)

eg. if I have a line of sales made in April at 1000, i would expect the formula to auto calculate when I selected respective slicer:
select April in Period - 1000 divide by 4

select Q2 in Quarter (ie. same as selecting April, May and June in Period) - 1000 divide by 13

select Q1 and Q2 in Quarter - 1000 divide by 26

select April, May in Period - 1000 divide by 8

and so on

I have tried different variations of formula but can only get either one of the results, I have linked the many to one relationships properly. Thanks!

[v-shex-msft · Accepted solution]

Hi @dsl98,

It sounds like you want to use slicer to apply selector effect instead of filter effects.

For this scenario, I'd like to suggest you create a disconnected table as source of slicer, then you can add variables in the Dax expression to calculate the current total volumes and selected range volumes.

SELECTEDVALUE function - DAX | Microsoft Learn

Regards,

Xiaoxin Sheng