DAX Expression - Return Max Date x Groups

[kent-culpepper]

I am running into a problem with an aggregation re: a closing\ ending balance for the max months by quarter.

I have illustrated the issue below and provided my current DAX expression (which is working fine when the user is selecting to view by Month but overstating the balance\value when selecting to view quarters).

AV_Last_N_Period =

VAR Selected_Period = SELECTEDVALUE(Calendar_Period[Period_XRef])

VAR Selected_Horizon = SELECTEDVALUE(Calendar_Horizon[No_Periods])

VAR Selected_Month_Date = MAX('Calendar'[Month_Date])

VAR Month_Date_Horizon = DATESINPERIOD(Calendar_Filter_Month[Month_Date],Selected_Month_Date,Selected_Horizon,MONTH)

VAR Quarter_Date_Horizon = DATESINPERIOD(Calendar_Filter_Quarter[Month_Date],Selected_Month_Date,Selected_Horizon,QUARTER)

VAR Result =

IF(Selected_Period = "Months",

CALCULATE(

SUM(AUM[Asset_Value]),

REMOVEFILTERS('Calendar'),

KEEPFILTERS(Month_Date_Horizon),

USERELATIONSHIP('Calendar'[Month_Date],Calendar_Filter_Month[Month_Date])

),

CALCULATE(

SUM(AUM[Asset_Value]),

REMOVEFILTERS('Calendar'),

KEEPFILTERS(Quarter_Date_Horizon),

USERELATIONSHIP('Calendar'[Month_Date],Calendar_Filter_Quarter[Month_Date])

))

RETURN

Result

[kent-culpepper · Accepted solution]

Thanks for response.

I added a column on my facts table for CLOSINGBALANCEQUARTER and this seems to solve it.

[Abhinav054]

Ok. In this i have added BLANK() filter, so it ensures tables are empty when the user selects to view by a different period

AV_Last_N_Period =
VAR Selected_Period = SELECTEDVALUE(Calendar_Period[Period_XRef])
VAR Selected_Horizon = SELECTEDVALUE(Calendar_Horizon[No_Periods])
VAR Selected_Month_Date = MAX('Calendar'[Month_Date])

VAR Month_Date_Horizon =
IF(
Selected_Period = "Months",
DATESBETWEEN(
Calendar_Filter_Month[Month_Date],
Selected_Month_Date,
EDATE(Selected_Month_Date, -Selected_Horizon + 1),
MONTH
),
BLANK()
)

VAR Quarter_Date_Horizon =
IF(
Selected_Period = "Quarters",
DATESBETWEEN(
Calendar_Filter_Quarter[Month_Date],
Selected_Month_Date,
EDATE(Selected_Month_Date, -Selected_Horizon + 1),
QUARTER
),
BLANK()
)

VAR Result =
IF(
Selected_Period = "Months",
CALCULATE(
SUM(AUM[Asset_Value]),
REMOVEFILTERS('Calendar'),
KEEPFILTERS(Month_Date_Horizon),
USERELATIONSHIP('Calendar'[Month_Date], Calendar_Filter_Month[Month_Date])
),
CALCULATE(
SUM(AUM[Asset_Value]),
REMOVEFILTERS('Calendar'),
KEEPFILTERS(Quarter_Date_Horizon),
USERELATIONSHIP('Calendar'[Month_Date], Calendar_Filter_Quarter[Month_Date])
)
)

RETURN
Result

[kent-culpepper]

No that doesnt work.

[Abhinav054]

hii, try this code

AV_Last_N_Period =
VAR Selected_Period = SELECTEDVALUE(Calendar_Period[Period_XRef])
VAR Selected_Horizon = SELECTEDVALUE(Calendar_Horizon[No_Periods])
VAR Selected_Month_Date = MAX('Calendar'[Month_Date])
VAR Month_Date_Horizon = DATESBETWEEN(
Calendar_Filter_Month[Month_Date],
Selected_Month_Date,
EDATE(Selected_Month_Date, -Selected_Horizon + 1),
MONTH
)
VAR Quarter_Date_Horizon = DATESBETWEEN(
Calendar_Filter_Quarter[Month_Date],
Selected_Month_Date,
EDATE(Selected_Month_Date, -Selected_Horizon + 1),
QUARTER
)
VAR Result =
IF(
Selected_Period = "Months",
CALCULATE(
SUM(AUM[Asset_Value]),
REMOVEFILTERS('Calendar'),
KEEPFILTERS(Month_Date_Horizon),
USERELATIONSHIP('Calendar'[Month_Date], Calendar_Filter_Month[Month_Date])
),
CALCULATE(
SUM(AUM[Asset_Value]),
REMOVEFILTERS('Calendar'),
KEEPFILTERS(Quarter_Date_Horizon),
USERELATIONSHIP('Calendar'[Month_Date], Calendar_Filter_Quarter[Month_Date])
)
)
RETURN
Result