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 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
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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • 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



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

    Thanks for response.

     

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