Forum Discussion

IrisW's avatar
IrisW
Regular Visitor
1 year ago
Solved

Difficult sum

In my report, I have a date filter. With this I select one date, for example Dec. 31, 2023.

 

My source data is structured as follows:

 

IDStartdateUntildateValue
1Nov. 1, 2023Nov. 2, 202340
1Nov. 2, 2023Nov. 3, 202350
1Nov. 3, 2023Nov. 4, 202360
1Feb. 1, 2024Feb. 2, 202480
2Nov. 2, 2023Nov. 3, 202350
2Nov. 3, 2023Nov. 4, 202340
3Nov. 13, 2023Nov. 14, 202350
3Nov. 14, 2023Nov. 15, 202360

 

Power BI should give me the sum of all the Values where the date in the Untildate column is largest (per ID) under the selected date. So in this example, 60+40+60 = 160.

 

IDStartdateUntildateValue
1Nov. 1, 2023Nov. 2, 202340
1Nov. 2, 2023Nov. 3, 202350
1Nov. 3, 2023Nov. 4, 202360
1Feb. 1, 2024Feb. 2, 202480
2Nov. 2, 2023Nov. 3, 202350
2Nov. 3, 2023Nov. 4, 202340
3Nov. 13, 2023Nov. 14, 202350
3Nov. 14, 2023Nov. 15, 202360

 

Is this possible?

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    IrisW ,  Create a measure to find the largest Untildate per ID that is under the selected date.
    Create another measure to sum the values corresponding to these largest Untildate values.

     

    DAX
    Max_Untildate_Under_Selected =
    CALCULATE(
    MAX('Table'[Untildate]),
    FILTER(
    'Table',
    'Table'[Untildate] <= SELECTEDVALUE('Date'[Date])
    )
    )

    Sum_Values_Largest_Untildate =
    SUMX(
    SUMMARIZE(
    'Table',
    'Table'[ID],
    "Max_Untildate", [Max_Untildate_Under_Selected]
    ),
    CALCULATE(
    SUM('Table'[Value]),
    'Table'[Untildate] = [Max_Untildate]
    )
    )