How to use a measure for the first time-period selected for other time periods

[Anonymous]

Hello everyone,

I'm working on a solution to calculate the churn, which in this case I define as the total in- or out-flows in a given month divided by the AUM in the first period.  The in- and out-flows are a summation of all the transactions in a given month (TransactionsFact table). Similarly, the AUM is a summation of the all the AUM across many categories for a given month.  So in this case, July 2021 inflow churn should be 1,019 / 160,165. However, when I calculate inflow churn for July 2021, I cannot use the AUM for May 2021.

Here are my formulas:

AUM = CALCULATE(sum(AUM[Beginning AUM]),filter(AUM,AUM[Month]=MIN(AUM[Month])))

Churn Inflow % = Measure_Table_Churn[Churn Inflow Total] / [AUM]

Unfortunately I cannot upload the file, but I was hoping someone else has run into this situation and might be able to assist.

Thanks!

[PaulDBrown]

You might have issues with the many-to-many relationship. In other words, it is highly recommended to avoid these kinds of relationships (you could include other relevant fields in the new dimension table, just keeping the month field unique)

As for the measure, try:

AUM =
VAR _Year =
    MAX ( 'DateDim'[Year] )
VAR MinM =
    CALCULATE (
        MIN ( 'DateDim'[Month] ),
        FILTER ( ALLSELECTED ( 'DateDim' ), 'DateDim'[Year] = _Year )
    )
RETURN
    CALCULATE (
        SUM(AUM[Beginning AUM]),
        FILTER ( ALLSELECTED ( 'DateDim'[Month] ), 'DateDim'[Month] = MinM )
    )

[Anonymous]

You have been so helpful, I greatly appreciate it, Paul!

As you suggested, I created a new DateDim_ table to support the one-to-many relationship. My AUM table, [Month] field is actually an end of the month date, so I had to update the DAX slightly:

BaseAUM =

VAR _Year =

MIN (DateDim_measure[YEAR])

VAR MinM =

CALCULATE(

MIN (DateDim_measure[End of Month]),

FILTER (ALLSELECTED( DateDim_measure), DateDim_measure[Year] = _Year)

)

RETURN

CALCULATE(

SUM(AUM[Beginning AUM]),

FILTER(ALLSELECTED(DateDim_measure[End of Month]),DateDim_measure[End of Month] = MinM)

)

If I don't change years, the Base AUM works billiantly. However, as soon as I crossover years, the Base AUM reverts to the earliest month of that year. Any suggestions how to update the DAX?

Kevin

[PaulDBrown · Accepted solution]

Sorry, I thought that was what you wanted: the calculation to reset each year.
If you want the calculation to ignore the year (so the measure computes the minimum period in the selected periods), try:

BaseAUM =
VAR MinM =
    CALCULATE (
        MIN ( DateDim_measure[End of Month] ),
        ALLSELECTED ( DateDim_measure )
    )
RETURN
    CALCULATE (
        SUM ( AUM[Beginning AUM] ),
        FILTER (
            ALLSELECTED ( DateDim_measure[End of Month] ),
            DateDim_measure[End of Month] = MinM
        )
    )

[Anonymous]

No need to apologize, you have saved my forehead from more head banging. This worked perfectly. I appreciate your time and replies.

[PaulDBrown]

Is the month field in the matrix from the date table? Also, is the year relevant in the calculation (if your data spans more than 1 year)?
I take it you have a many-many relationship between the date table and the AUM table because the common field is month, right?

If so, I would create a new dimension table with unique month values and then create a one-to-many relationship between this new dimension table and the date table and AUM tables (and delete the many-to-many relationship between the date table and the AUM table). You can then use the field from this month dimension table.

[Anonymous]

Paul - Yes, the month field in the matrix is a formatted date, DateDim[End of Month] from the DateDim (date). Also, the year is relevant because this will span 2020 to 2021, and eventually into 2022 and beyond. 

I am using the DateDim[End of Month] field as a slicer to select any relevant time period. So I want this to dynamically change based on the slicer period selected.