Forum Discussion

Siddharth_07's avatar
Siddharth_07
Frequent Visitor
4 years ago
Solved

Market Value as on date

Hello,

I have a doubt, if we want to see the values ​​as on date, then how can we get the MV values ​​as on date? I attach the Data. For your reference.

Greetings

Siddharth Jain
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Siddharth_07 

    Try this,

    (1) create a calendar table

    calendar = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

    (2) Dax code:

    MeanValue = //Measure
    var _startDate= CALCULATE(MIN('calendar'[Date]),ALLSELECTED('calendar'))
    var _endDate= CALCULATE(MAX('calendar'[Date]),ALLSELECTED('calendar'))
    var _count=CALCULATE(DISTINCTCOUNT('Table'[Valuation Date]),FILTER(ALL('Table'),'Table'[Valuation Date]>=_startDate && 'Table'[Valuation Date] <= _endDate))
    var _total=CALCULATE(SUM('Table'[Value Reff]),FILTER(ALL('Table'),'Table'[Valuation Date]>=_startDate && 'Table'[Valuation Date] <= _endDate))
return DIVIDE(_total,_count)

    result

     

     

    Best Regards,

    Community Support Team _Tang

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

3 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Siddharth_07 

    Try this,

    (1) create a calendar table

    calendar = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

    (2) Dax code:

    MeanValue = //Measure
    var _startDate= CALCULATE(MIN('calendar'[Date]),ALLSELECTED('calendar'))
    var _endDate= CALCULATE(MAX('calendar'[Date]),ALLSELECTED('calendar'))
    var _count=CALCULATE(DISTINCTCOUNT('Table'[Valuation Date]),FILTER(ALL('Table'),'Table'[Valuation Date]>=_startDate && 'Table'[Valuation Date] <= _endDate))
    var _total=CALCULATE(SUM('Table'[Value Reff]),FILTER(ALL('Table'),'Table'[Valuation Date]>=_startDate && 'Table'[Valuation Date] <= _endDate))
return DIVIDE(_total,_count)

    result

     

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, 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
    4 years ago

    Siddharth_07 , Create an independent date table and use that as a slicer

     

    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _max = maxx(filter(allselected(Table) , Table[Valuation Date] <=_max), [Valuation Date])
    return
    calculate( sum(Table[Value Reff]), filter('Table', 'Table'[Valuation Date]=_max))

     

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • Siddharth_07's avatar
      Siddharth_07
      Frequent Visitor
      4 years ago

      I wanted to add in the graphs as well so can I add this slicer in graphs as well amitchandak? Could you do the same in power bi and provide me with the file?

       

      Regards,

      Siddharth Jain