Forum Discussion

ghost10's avatar
ghost10
Frequent Visitor
3 years ago
Solved

Range end close price

Hi Team ,

I am working working on some power bi reports.

Actually I want to know how can we derive close end price for the perticular month based on id and date.

using dax only.

 

data protection
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ghost10 ,

     

    Due to I don't know your data model, I create a sample to have a test.

    My Sample:

    YearMonth is a calcualted column.

    YearMonth = YEAR([Date])*100+MONTH([Date])

    Measure:

    Range end close price =
CALCULATE (
    SUM ( 'Table'[Price] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[YearMonth] ),
        'Table'[Date] = MAX ( 'Table'[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.

     

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    ghost10 Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • ghost10's avatar
      ghost10
      Frequent Visitor
      3 years ago

      I have posted sample report, please have a look

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

    ghost10 ,

     

    MTD Last Price= CALCULATE(lastnonblankvalue(Date[Date], Max(Table[Price])) ,DATESMTD('Date'[Date]))

     

     

    This Month =
    var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
    var _min = eomonth(_max,-1)+1 ,
    return
    CALCULATE(lastnonblankvalue(Date[Date], Max(Table[Price])) ,DATESBETWEEN('Date'[Date],_min,_max))

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ghost10 ,

     

    Due to I don't know your data model, I create a sample to have a test.

    My Sample:

    YearMonth is a calcualted column.

    YearMonth = YEAR([Date])*100+MONTH([Date])

    Measure:

    Range end close price =
CALCULATE (
    SUM ( 'Table'[Price] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[YearMonth] ),
        'Table'[Date] = MAX ( 'Table'[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.