Forum Discussion

GlynMThomas's avatar
GlynMThomas
Icon for Resolver I rankResolver I
7 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 ...
Need Help
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
7 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
Icon for Resolver I rankResolver I
7 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.