Forum Discussion

mykolasg's avatar
mykolasg
New Member
4 years ago
Solved

Max date in selected week

Hello

 

i have the following data. Final purpose is to select week and get Balance of that weeks max date. Looking forward to suggestions.

 

DateWeek NumberVendorBalance
2022-01-031Amazon1000
2022-01-041Amazon95
2022-01-051Amazon400
2022-01-061Amazon900
2022-01-071Amazon1500
2022-01-081Amazon1999
2022-01-091Amazon10000
2022-01-102Amazon1000
2022-01-112Amazon95
2022-01-122Amazon400
2022-01-132Amazon900
2022-01-142Amazon1500
2022-01-152Amazon1999
2022-01-162Amazon10000
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi mykolasg ,

     

    I think you want to calculate the date of max balance in select week. You can try this code.

    Max Date select week =
VAR _SELECT_WEEK =
    SELECTEDVALUE ( 'Dim Week Number'[Week Number] )
VAR _MAX_Balance =
    CALCULATE (
        MAX ( 'Table'[Balance] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Week Number] = _SELECT_WEEK )
    )
VAR _MAX_Date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Week Number] = _SELECT_WEEK
                && 'Table'[Balance] = _MAX_Balance
        )
    )
RETURN
    _MAX_Date

    Result is as below.

    Best Regards,
    Rico Zhou

     

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

4 Replies

  • truptis's avatar
    truptis
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi mykolasg ,

    Try this:

    Result = CALCULATE(LASTNONBLANKVALUE(Tablename[Date], sum(Tablename[Balance])))

     

     mykolasg please mark this as a solution & hit the thumbs up if it helps you. Thanks.

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    mykolasg , Try like

    calculate(lastnonblankvalue(Table[Date], sum(Table[Balance])))

     

    Use allexcept if needed

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure how your desired outcome visualization looks like, but please check the below picture and the attached pbix file.

     

     

    Max date balance: = 
LASTNONBLANKVALUE ( Data[Date], SUM ( Data[Balance] ) )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi mykolasg ,

     

    I think you want to calculate the date of max balance in select week. You can try this code.

    Max Date select week =
VAR _SELECT_WEEK =
    SELECTEDVALUE ( 'Dim Week Number'[Week Number] )
VAR _MAX_Balance =
    CALCULATE (
        MAX ( 'Table'[Balance] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Week Number] = _SELECT_WEEK )
    )
VAR _MAX_Date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Week Number] = _SELECT_WEEK
                && 'Table'[Balance] = _MAX_Balance
        )
    )
RETURN
    _MAX_Date

    Result is as below.

    Best Regards,
    Rico Zhou

     

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