Calculated measure excluding slicer

[lakshay12311]

Hello all,

I have a scenario where I have 3 tables – 1 fact( named Actual) and 2 dimensions( Calendar and Account Dim). These are connected via Account ID and Date ID.

I have a required to create 2 measure :

1. Total 3 months moving Avg for Account Cat= CALCULATE('Actual'[3Mnths Moving Avg],Account[Account Desc]="Cat")
2. Value on Hand= DIVIDE(SUM('Actual'[Value]), Actual[Total 3 months moving Avg for Account Cat])

I want to get below table values. I am unable to get these. Can someone please suggest on what DAX needs to be changed?

	Total 3 months moving Avg for Account Cat	Value	Value on Hand
Apple	1800	300	0.166666667
Boy	1800	400	0.222222222
Cat	1800	600	0.333333333

Sample data for Actual is :

Date ID	Account ID	Value	3Mnths Moving Avg
1	A	100	500
1	A	200	600
1	B	300	700
1	B	100	500
1	C	100	600
1	C	200	700
1	C	300	500

Sample data for Account Dim:

Account ID	Account Desc
A	Apple
B	Boy
C	Cat

Thanks,

Lakshay

[AnkitKukreja]

Hi @lakshay12311 

Try something like this

CALCULATE ( AVERAGEX ( VALUES ( 'Date'[YearMonth] ), [Sales] ), DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -3, MONTH ) )

Thanks,

Ankit