Forum Discussion

GlynMThomas's avatar
GlynMThomas
Resolver I
6 years ago

Get Valuation Based On Max Date In Slicer

I have valuations split by adviser for every work day going back 3 years (Essentially a valuation for every day dated). I need to add this to a table of transactions being aggregated in power bi. To do this I need to specify the the date I want the valuation for as doing a between will sum several days up and give me a wrong valuation. I've written a measure to do this and it works fine for the max date, however if I move the slicer max date below this it brings back blanks. I'm slightly confused as I haven't set a max on the table it's calculating only the date and if I put this into a card to see which date comes back it's changes with the slicer correctly. My measure is:

 

Measure = var MaxDate = CALCULATE(MAX('Money Movement Transaction'[tfr_create_date_key]),ALLSELECTED('Money Movement Transaction'))

var TotalAUM = CALCULATE(SUM('dwvw Get AUM Solution History'[FundValue]), FILTER(ALL('dwvw Get AUM Solution History'), 'dwvw Get AUM Solution History'[date_key] = MaxDate))

RETURN TotalAUM
 
My table looks something like:
date_key     FundValue     adviser_key
20190901   20000             1242
20190902   35200             1242
20190901   12000             4521
 
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Is your slicer on the Money Movement Transaction'[tfr_create_date_key])? 

    Then if should reflect the change. The only thing needs to be checked that there no other date filter impacting this calculation.

     

    Whenever filter is used in a calculation, we need to make sure it does not follow the original date filter. Example

     

    Sales Before QTR = 
CALCULATE(SUMx(FILTER(sales,and(Sales[Order_Date]>= STARTOFQUARTER(DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY)) && Sales[Order_Date]<= DATEADD(STARTOFQUARTER(OrderTime[Order Date]),-1,DAY),
Sales[Requested_Date]>= STARTOFQUARTER(OrderTime[Order Date]) && Sales[Requested_Date]<= STARTOFQUARTER(OrderTime[Order Date])
)),Sales[Sales]),CROSSFILTER(Sales[Order_Date],OrderTime[Order Date],None))

     

     

    • GlynMThomas's avatar
      GlynMThomas
      Resolver I
      6 years ago

      No the slicer is using the full date from my date dimension, but the dimension is connected via the surrogate date_key to the tfr_create_date_key. My valuation fact also has a surrogate date key, so in my head the date selected from the slicer should set the correct date key to be used to pull the correct valuation. But it looks like it's pulling the top date key ever regardless and then the valuations get set to blank if I move past this date in the slicer.