Forum Discussion

M_SBS_6's avatar
M_SBS_6
Icon for Helper V rankHelper V
2 years ago
Solved

Latest Dates Sum

Hi, 

 

I have a table of data below. I get the bottom 5 mort_name files daily but the top row, TRR, the data is sporadic. 

 

Due to getting data daily for the 5 mort_names, I want to sum up the mort_val based on the latest date. In this example I return 14000.

 

Mort_date. Mort_name Mort_val

28/06/2024. TRR. 2000

20/08/2024. TAA. 1000

20/08/2024. ZZR. 3000

20/08/2024. HWE. 4000

20/08/2024. MVE. 4000

20/08/2024. TSR. 2000

 

However, that isn't quite right as to get the total mort_val i need, I also need to include the 2000 for TRR, so the latest date function doesn't work. 

 

What I need is to return the mort_value based on the mort_name and their individual latest/max mort_date. 

 

Mort_name TRR might come in today (20/08/2024) and using latest date would work as i have date for all mort_name but when I report tomorrow (21/08/2024), the date becomes outdated again as TRR data unlikely to be available for 2 weeks down the line. 

 

I hope this makes sense, any help is appreciated.

Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi M_SBS_6 

     

    Thank you for the sample data. Honestly, though it would have been easier if you included not just data with the latest date and your expected result from that data. Nonetheless, here's my shot at this.

    Mort Value based on Latest Mort Date Measure = 
VAR LatestDate =
    --latest date by mort_name
    CALCULATE (
        MAX ( 'Table'[Mort_date] ),
        ALLEXCEPT ( 'Table', 'Table'[Mort_name ] )
    )
RETURN
    --mort_name value of the latest mort_date
    CALCULATE (
        SUM ( 'Table'[Mort_val] ),
        KEEPFILTERS ( 'Table'[Mort_date] = LatestDate )
    )

     

    Please refer to the sample pbix for the details.

1 Reply

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    2 years ago

    Hi M_SBS_6 

     

    Thank you for the sample data. Honestly, though it would have been easier if you included not just data with the latest date and your expected result from that data. Nonetheless, here's my shot at this.

    Mort Value based on Latest Mort Date Measure = 
VAR LatestDate =
    --latest date by mort_name
    CALCULATE (
        MAX ( 'Table'[Mort_date] ),
        ALLEXCEPT ( 'Table', 'Table'[Mort_name ] )
    )
RETURN
    --mort_name value of the latest mort_date
    CALCULATE (
        SUM ( 'Table'[Mort_val] ),
        KEEPFILTERS ( 'Table'[Mort_date] = LatestDate )
    )

     

    Please refer to the sample pbix for the details.