Solved: LastDate filter problem

fjjpeeters1976 · ‎07-01-2025

Hi all,

I need to calculate an allocation factor based on three parts: sales/balancesheet and costs.

This allocation factor is only calculated once a year (in this case 2024) and than used for calculation with 2025 costs. So I want to incorporate the 2024 filter in below calculations (and later a selectedvalue on the year) and I have it working for sales and costs.

The only issue I have is to get it working for the balancesheet items, as this is non additive measure. That is why I use a lastdate function. When I put in the filter on the visual it works, but I want to have it incorporated in the measure.

I tried already to wrap it in a calculate like Calculate(LastDate('Date'[Date]), 'Date'[Year]="2024") but then it gives me an error message that the true/false expression does not specify a column.

Does anyone have a solution how to solve this issue?

Test =

VAR full =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

'Date'[Year] = 2024,

DimAccountDetailed[Net Sales Total] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

)

VAR BLS =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

'Date'[Year] = 2024,

DimAccountDetailed[Net Sales Total] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

)

),

'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

)

VAR NetResult = DIVIDE(BLS, full) * Factor[Net sales factor]

-- Balance Sheet Section

VAR balancesheet =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

DimAccountDetailed[BalanceSheet] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

),

LASTDATE('Date'[Date])

)

VAR balan =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

DimAccountDetailed[BalanceSheet] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

),

LASTDATE('Date'[Date])

),

'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

)

VAR Bal = DIVIDE(balan, balancesheet) * Factor[Prod.asset factor]

-- P&L Section

VAR profit =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

'Date'[Year] = 2024,

DimAccountDetailed[P&L] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

)

VAR prof =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

'Date'[Year] = 2024,

DimAccountDetailed[P&L] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

)

),

'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

)

VAR ProResult = DIVIDE(prof, profit) * Factor[Conv.cost factor]

-- Final Result

VAR result = NetResult + Bal + ProResult

RETURN

result

v-agajavelly · ‎07-01-2025

Hi @fjjpeeters1976

Great question, I’ve run into this challenge before, especially when working with non-additive measures like balance sheet values that need to be pulled from a specific date (like year-end). You're on the right track using LASTDATE() the main issue comes from how you're applying the year filter. This line,

Test = CALCULATE(LASTDATE('Date'[Date]), 'Date'[Year] = 2024)

throws an error because DAX doesn't accept boolean expressions directly like that inside CALCULATE.

Here's a working approach that will let you restrict LASTDATE to just 2024.

Test 1 = CALCULATE(LASTDATE('Date'[Date]),FILTER(ALL('Date'), 'Date'[Year] = 2024))

This tells DAX to look at all dates (ignoring any visual filters), and then limit it to dates in 2024 before returning the last one. It's the safest way to apply a year filter inside a CALCULATE.

Then apply it inside your measure like this.

VAR lastDate2024 = CALCULATE(LASTDATE('Date'[Date]),FILTER(ALL('Date'), 'Date'[Year] = 2024))

And replace your original LASTDATE(...) lines with lastDate2024.

If you plan to make this dynamic in the future (e.g., based on a slicer), just define a selected year like this:

VAR SelectedYear = SELECTEDVALUE('Date'[Year], 2024)

And update the filter to.

FILTER(ALL('Date'), 'Date'[Year] = SelectedYear)

Hope this helps make your allocation factor work well with balance sheet items.

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Thanks,
Akhil.

View solution in original post

Poojara_D12 · ‎07-04-2025

Hi @fjjpeeters1976

You're calculating an allocation factor in Power BI based on three components: Sales, Balance Sheet, and P&L. The goal is to calculate this factor only once for a specific year (e.g., 2024), then use it in calculations for subsequent periods like 2025. You've successfully applied the 2024 filter to your sales and P&L parts using 'Date'[Year] = 2024. However, you're running into issues applying the same year filter in the Balance Sheet section because that measure is non-additive and relies on a specific date (typically the last date of the year).

The challenge arises because LASTDATE('Date'[Date]) returns a scalar value (a single date), but you're attempting to use 'Date'[Year] = 2024 as a Boolean filter in a context where a column is expected. The error you're seeing is due to Power BI not being able to resolve 'Date'[Year] = 2024 as a valid table filter within CALCULATE.

To resolve this, instead of using the 'Date'[Year] = 2024 filter directly, you should create a virtual date table filtered to the desired year and pass that into LASTDATE. Here’s how you can update your balancesheet and balan variables:

VAR lastDate2024 =
    CALCULATE(
        LASTDATE('Date'[Date]),
        FILTER(ALL('Date'), 'Date'[Year] = 2024)
    )

Then use this scalar value inside your existing Balance Sheet section as:

VAR balancesheet =
    CALCULATE(
        [Amount Adjusted],
        DimScenarioDetailed[Scen_Level1] = "FA",
        DimAccountDetailed[BalanceSheet] = 1,
        TREATAS(
            VALUES('AllocationKey Man.Fee'[Entitycode]),
            DimEntity[Entity_flat_NodeName]
        ),
        'Date'[Date] = lastDate2024
    )

VAR balan =
    CALCULATE(
        CALCULATE(
            [Amount Adjusted],
            DimScenarioDetailed[Scen_Level1] = "FA",
            DimAccountDetailed[BalanceSheet] = 1,
            TREATAS(
                VALUES('AllocationKey Man.Fee'[Entitycode]),
                DimEntity[Entity_flat_NodeName]
            ),
            'Date'[Date] = lastDate2024
        ),
        'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"
    )

By explicitly filtering 'Date'[Date] to the lastDate2024 value, you effectively lock the Balance Sheet logic to the correct end-of-year snapshot. This avoids relying on slicers or visuals for filtering and ensures consistent, hardcoded year-specific logic for allocation.

This adjustment will now allow the entire allocation factor calculation to be based purely on 2024 data, and reused consistently for 2025 or any future year logic without dependency on external filters.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos"

Kind Regards,
Poojara - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

v-agajavelly · ‎07-04-2025

Hi @fjjpeeters1976 ,

Thanks for the update. Glad to hear you were able to modify and resolve the issue successfully. Your revised DAX approach looks solid especially how you've isolated the balance sheet values with precise filters and incorporated TREATAS for entity alignment. Nicely done including the DIVIDE logic at the end as well that keeps it robust and avoids divide-by-zero issues. Appreciate you sharing the working solution back here and accept this will definitely help others in the community similar challenges.

Regards,
Akhil.

v-agajavelly · ‎07-01-2025

Hi @fjjpeeters1976

Great question, I’ve run into this challenge before, especially when working with non-additive measures like balance sheet values that need to be pulled from a specific date (like year-end). You're on the right track using LASTDATE() the main issue comes from how you're applying the year filter. This line,

Test = CALCULATE(LASTDATE('Date'[Date]), 'Date'[Year] = 2024)

throws an error because DAX doesn't accept boolean expressions directly like that inside CALCULATE.

Here's a working approach that will let you restrict LASTDATE to just 2024.

Test 1 = CALCULATE(LASTDATE('Date'[Date]),FILTER(ALL('Date'), 'Date'[Year] = 2024))

This tells DAX to look at all dates (ignoring any visual filters), and then limit it to dates in 2024 before returning the last one. It's the safest way to apply a year filter inside a CALCULATE.

Then apply it inside your measure like this.

VAR lastDate2024 = CALCULATE(LASTDATE('Date'[Date]),FILTER(ALL('Date'), 'Date'[Year] = 2024))

And replace your original LASTDATE(...) lines with lastDate2024.

If you plan to make this dynamic in the future (e.g., based on a slicer), just define a selected year like this:

VAR SelectedYear = SELECTEDVALUE('Date'[Year], 2024)

And update the filter to.

FILTER(ALL('Date'), 'Date'[Year] = SelectedYear)

Hope this helps make your allocation factor work well with balance sheet items.

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Thanks,
Akhil.

fjjpeeters1976 · ‎07-02-2025

Hi Akhil,

I have changed the calculation as following, however I still get the same error message.

-- Balance Sheet Section

VAR lastD = CALCULATE(LASTDATE('Date'[Date]), FILTER(ALL('Date'), 'Date'[Year]=2024))

VAR balancesheet =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

DimAccountDetailed[BalanceSheet] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

),

lastD

)

VAR balan =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

DimAccountDetailed[BalanceSheet] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

),

lastD

),

'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

)

fjjpeeters1976 · ‎07-02-2025

I think I solved the issue by modifying it a little, as seen below

-- Balance Sheet Section

VAR lastD = CALCULATE(LASTDATE('Fact'[Date]), FILTER(ALL('Date'), 'Date'[Year]=2024))

VAR balancesheet =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

DimAccountDetailed[BalanceSheet] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

),

'Date'[Date]=lastD

)

VAR balan =

CALCULATE(

[Amount Adjusted],

DimScenarioDetailed[Scen_Level1] = "FA",

DimAccountDetailed[BalanceSheet] = 1,

TREATAS(

VALUES('AllocationKey Man.Fee'[Entitycode]),

DimEntity[Entity_flat_NodeName]

),

'Date'[Date]=lastD

),

'AllocationKey Man.Fee'[BA-Allocation] = "BAE_BLS"

)

VAR Bal = DIVIDE(balan, balancesheet) * Factor[Prod.asset factor]

FBergamaschi · ‎07-01-2025

Hi,

LASTDATE returns a table so it cannot be the first input of CALCULATE

If you paste here some data form all the tables involved, we can help you

Greg_Deckler · ‎07-01-2025

@fjjpeeters1976 Sounds like you have a non-additive measure. In that case, you probably need something like the following: Measure Totals, The Final Word - Microsoft Fabric Community

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: DAX For Humans

DAX is easy, CALCULATE makes DAX hard...