DAX to create totals dependant on plan and date

[SarahESkells]

Hello

I'm trying to recreate something I've built manually in Excel in Power BI, I think what I want to do is achievable, however I'm not sure I can get the DAX correct so looking for help.

The data:

A list of plans with valuations and valuation dates and current status, each plan can appear more than once, however it can only have one valuation per month, it could also have no valuations in a month

Row ID	Plan ID	Valuation	Valuation Date	Current Status
1	1	100	01/01/2025	Active
2	1	150	01/02/2025	Active
3	1	160	01/03/2025	Active
4	2	200	01/12/2024	Inactive
5	2	210	01/01/2025	Inactive
6	2	220	01/02/2025	Inactive
7	3	150	01/12/2024	Active
8	3	140	01/02/2025	Active
9	3	145	01/03/2025	Active

Desired Output

I want to create a table that calculates the total valuation each month based on the latest valuation for each plan - whether in that month or a previous month. If a plan is now inactive the value should not be included once the Date passes the last valuation date recorded (e.g. row 6 above has a last value date of 1st Feb, so isn't included in the 28th Feb total below)

Date	Total Value	Notes (note part of output)
31/12/2024	350	Row 4 + Row 7
31/01/2025	460	Row 1 + Row 5 + Row 7
28/02/2025	290	Row 2 + Row 8
31/03/2025	305	Row 3 + Row 9

Any help with creating this so it can be automated would be appreciated.

[v-hashadapu]

Hi @SarahESkells , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @SarahESkells , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[FarhanJeelani]

Hi @SarahESkells ,

Please try the below step:

Step 1: Create a Date Table
Ensure you have a Date Table in Power BI that spans the range of dates in your dataset. You can create this table using DAX like this:

DateTable = CALENDAR(MIN(YourTable[Valuation Date]), MAX(YourTable[Valuation Date]))

This Date Table should also include columns for the year, month, and day to facilitate easier calculations. You can use the YEAR(), MONTH(), and DAY() functions to extract these values.

Step 2: Calculate the Latest Valuation for Each Plan
You'll need to calculate the most recent valuation for each plan up to the current row's date. This can be achieved using the CALCULATE() and MAXX() functions.

Create a new measure that calculates the latest valuation for each plan:

Latest Valuation =
VAR MaxDate =
CALCULATE(
MAX(YourTable[Valuation Date]),
FILTER(
YourTable,
YourTable[Plan ID] = EARLIER(YourTable[Plan ID]) &&
YourTable[Valuation Date] <= MAX(DateTable[Date])
)
)
RETURN
CALCULATE(
MAX(YourTable[Valuation]),
YourTable[Valuation Date] = MaxDate,
YourTable[Plan ID] = EARLIER(YourTable[Plan ID])
)

Step 3: Filter Out Inactive Plans
You can add a condition to exclude valuations for inactive plans that have passed their last valuation date. Use the IF() condition to check whether the plan is active on the current date:

Valuation To Include =
IF(
YourTable[Current Status] = "Active" &&
YourTable[Valuation Date] <= MAX(DateTable[Date]),
YourTable[Valuation],
BLANK()
)

This ensures that only valuations for active plans (and within the appropriate date range) are included in the calculation.

Step 4: Summing the Total Value by Date
Now that you have a measure for the latest valuation for each plan, you can sum the values for each month.

Create another measure to calculate the total valuation for each month:

Total Value =
SUMX(
FILTER(
YourTable,
YourTable[Valuation Date] <= MAX(DateTable[Date]) &&
YourTable[Current Status] = "Active"
),
[Valuation To Include]
)

This measure sums the valuations for each plan based on the latest valuation for that plan up until the given date, filtering out inactive plans as needed.

Step 5: Display the Results in the Table
Now, you can create a table visual in Power BI with the Date column from the DateTable and the Total Value measure. This will automatically give you the monthly total of valuations, considering the most recent valuation for each active plan.

Please note: 

• You may need to adjust the DAX slightly depending on how your data is structured.
• If you have any more complexities like multiple valuations in the same month, or if there’s more logic to when a plan should be considered inactive, those can be incorporated into the DAX as well.

Please mark this post as solution if it hepls you. Appreciate Kudos.

[SarahESkells]

Thank you for your help, I've just tried this out, however failing at step 2 with the following error:

EARLIER/EARLIEST refers to an earlier row context which doesn't exist.

It accepts the YourTable[PlanID] one side of the filter but not the EARLIER(YourTable[PlanID]) the rest of it looks ok though but I can't run it with that error

[FarhanJeelani]

Hi @SarahESkells ,

The error you are encountering with the EARLIER function occurs because there isn't an earlier row context in the measure you're trying to use it. This typically happens when you're not inside a row context (e.g., inside a table or calculated column).

To resolve this, you can adjust the DAX by using VAR to hold the current value of the Plan ID in your calculation, and then use that VAR inside the filter for the calculation. 

Revised Step 2 DAX:

Latest Valuation =
VAR MaxDate =
CALCULATE(
MAX(YourTable[Valuation Date]),
FILTER(
YourTable,
YourTable[Plan ID] = MAX(YourTable[Plan ID]) &&
YourTable[Valuation Date] <= MAX(DateTable[Date])
)
)
RETURN
CALCULATE(
MAX(YourTable[Valuation]),
YourTable[Valuation Date] = MaxDate &&
YourTable[Plan ID] = MAX(YourTable[Plan ID])
)

 Please mark this post as solution if it helps you. Appreciate kudos.

[SarahESkells]

Thank you, that has sorted out step 2 and I can run that, step 3 doesnt like any of the fields inside the IF statement - the only one it allows me to pick is the measure created in step 2? When I put in all the fields I get this error:

Any idea what I might be doing wrong?

Thanks again for your help with this one

[FarhanJeelani]

Hi @SarahESkells ,

The error "A single value for a column cannot be determined" happens when Power BI expects a single scalar value but encounters multiple rows. This typically occurs when trying to use a column directly in a measure without aggregation.

Fix for Step 3:
Try modifying your measure to ensure that an aggregation function (like MAX, SUM, or SELECTEDVALUE) is used where necessary.

Corrected Step 3 DAX:

Valuation To Include =
VAR LastValuationDate =
CALCULATE(
MAX(YourTable[Valuation Date]),
FILTER(
YourTable,
YourTable[Plan ID] = MAX(YourTable[Plan ID]) &&
YourTable[Valuation Date] <= MAX(DateTable[Date])
)
)

VAR LastValuation =
CALCULATE(
MAX(YourTable[Valuation]),
YourTable[Valuation Date] = LastValuationDate &&
YourTable[Plan ID] = MAX(YourTable[Plan ID])
)

RETURN
IF(
MAX(YourTable[Current Status]) = "Active" ||
LastValuationDate >= MAX(DateTable[Date]),
LastValuation,
BLANK()
)

Logic Correction:

Keeps the latest valuation up to the current date.
Excludes inactive plans after their last valuation date.

Next Step:
Once this measure works, use it inside your Total Value measure:

Total Value =
SUMX(
VALUES(YourTable[Plan ID]),
[Valuation To Include]
)

Please mark this post as solution if it helps you. Appreciate Kudos. 

[SarahESkells]

Thanks for this, I've managed to complete all the steps. I've just put it into a table though and the values are all showing as dates? There is no way for me to change this to a value either so I'm not sure what I've done wrong here?

[v-hashadapu · Accepted solution]

Hi @SarahESkells , Thank you for reaching out to the Microsoft Community Forum.

This is usually caused by either returning the wrong field in a measure like a date instead of a numeric value or Power BI interpreting the data type incorrectly. In your case, it looks like the “Valuation To Include” measure might be returning the valuation date rather than the amount or Power BI is just misreading the result due to the logic or formatting. There's also a small logic tweak needed to stop inactive plans from contributing after their last valuation date. Try below:

Valuation To Include =

VAR CurrentPlan = MAX(YourTable[Plan ID])

VAR CurrentDate = MAX(DateTable[Date])

VAR LastValuationDate =

    CALCULATE(

        MAX(YourTable[Valuation Date]),

        FILTER(

            YourTable,

            YourTable[Plan ID] = CurrentPlan &&

            YourTable[Valuation Date] <= CurrentDate

        )

    )

VAR LastValuation =

    CALCULATE(

        MAX(YourTable[Valuation]),

        YourTable[Plan ID] = CurrentPlan &&

        YourTable[Valuation Date] = LastValuationDate

    )

VAR PlanStatus =

    CALCULATE(

        MAX(YourTable[Current Status]),

        YourTable[Plan ID] = CurrentPlan &&

        YourTable[Valuation Date] = LastValuationDate

    )

RETURN

IF(

    PlanStatus = "Active" || LastValuationDate = CurrentDate,

    LastValuation,

    BLANK()

)

Then use this to calculate the total per month:

Total Value =

SUMX(

    VALUES(YourTable[Plan ID]),

    [Valuation To Include]

)

In the Modeling tab, set both measures to Currency or Decimal Number, Ensure Valuation is a numeric field and use the DateTable[Date] in your table visual alongside Total Value.

Also, please: Data types in Power BI Desktop

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.