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.

     

     

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))