need for last date with data for a project

[bvdm1980]

So i'm back with another Q. I thought i had it working properly, untill there came an exception which made clear my dax isn't returning the correct data. 

What is have, is a table with 1 column being the values of a measure, where the individual values are correct, but the total below the table for this column, isnt correct. If you sum the TT resultaat per datum column you will see it should total to 6.203.000 and not to 6.184.000, the difference is the 19k line for project 411490. And as you can see, the last column on right shows different date as the last result for this project was booked at 30-6-2024, where rest is 31-8-2024. 

The total of the column is just the standard total from the visual settings. No calculated total or whatsoever. But apparently it does show the project result for this one on individual line, which is correct, but it doesn't included it in the total of the column...

I used this formula: 

TT resultaat per datum =

    CALCULATE(

        sum('tussentijds resultaat'[Gecorrigeerd tussentijds resultaat]) + sum('tussentijds resultaat'[Gecorrigeerde verliesvoorziening]),

        LASTDATE('tussentijds resultaat'[Boekingsdatum]))

where it goes wrong with the red marked part. It takes as last date the booking date of the monthly result table. But apparently it takes 31-8-2024 and doesn't look at the individual results per project, which can be before that date. 

So how do i solve this, so that the 19k gets included in the total? It seems so weird that making an automated total, doesn't ad up the individual values...

[v-kaiyue-msft · Accepted solution]

Hi @bvdm1980 ,

Based on your description, I created this data:

There are two methods to choose from here:

Measure = 
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[Boekingsdatum] ), ALLSELECTED ( 'Table' ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[Gecorrigeerd tussentijds resultaat] )
            + SUM ( 'Table'[Gecorrigeerde verliesvoorziening] ),
        'Table'[Boekingsdatum] >= _mindate
    )

Measure 2 = 
SUMX(
    'Table',
    CALCULATE(
        SUM('Table'[Gecorrigeerd tussentijds resultaat]) + 
        SUM('Table'[Gecorrigeerde verliesvoorziening]),
        LASTDATE('Table'[Boekingsdatum])
    )
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Kedar_Pande]

Updated Measure

TT resultaat per datum =
CALCULATE(
SUM('tussentijds resultaat'[Gecorrigeerd tussentijds resultaat]) +
SUM('tussentijds resultaat'[Gecorrigeerde verliesvoorziening]),
FILTER(
'tussentijds resultaat',
'tussentijds resultaat'[Boekingsdatum] <= MAX('tussentijds resultaat'[Boekingsdatum])
)
)

[bvdm1980]

yeah that is sometimes where i don't understand the logics of dax. 

You compare the same column from the same table, where (in my logic) it just says that date should be equal or smaller than that same date...

So why and how does this do the trick? 

Thanks for your reply btw! Much appreciated 🙂

[Kedar_Pande]

The LASTDATE function returns the latest date in the context of your visual, which might not always reflect the correct date for individual projects.

By using MAX - you ensure that the measure looks at the correct date range for each project individually rather than relying on the overall context of the visual.

If this helped, a Kudos 👍 or Solution mark would be great!
Cheers,
Kedar Pande
www.linkedin.com/in/kedar-pande

[bvdm1980]

the error seems to be in that "last ttr" column, which is a measure that returns the last date where a result was booked from the table "tussentijds resultaat". 

The total of the "tt resultaat per datum" column should include all projects that have last booked result date. And now it only includes project results with last date 31-8-2024.

But if i try to include that in my dax, it gives errors or totally different values. 

[bvdm1980]

Oh it doesn't solve the trick tbh

gives me total different values per project and in total.