Forum Discussion

kent-culpepper's avatar
kent-culpepper
Frequent Visitor
2 years ago
Solved

DAX Expression - Return Max Date x Groups

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 wo...
Need Help
  • kent-culpepper's avatar
    kent-culpepper
    2 years ago

    Thanks for response.

     

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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