Forum Discussion

bvdm1980's avatar
bvdm1980
Icon for Helper II rankHelper II
2 years ago
Solved

need for last date with data for a project

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 be...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Kedar_Pande
Icon for Super User rankSuper User
2 years ago

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's avatar
    bvdm1980
    Icon for Helper II rankHelper II
    2 years ago

    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's avatar
      Kedar_Pande
      Icon for Super User rankSuper User
      2 years ago

      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's avatar
        bvdm1980
        Icon for Helper II rankHelper II
        2 years ago

        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's avatar
      bvdm1980
      Icon for Helper II rankHelper II
      2 years ago

      Oh it doesn't solve the trick tbh

      gives me total different values per project and in total.