Forum Discussion
Related filtering with years
Hi everyone,
I hope u can help me with this.
These are my two tables.
Sales ant Time and I have a measure that is:
TotSales = CALCULATE( SUM(Sales[SalesAmount]) )
My canvas:
According my filter i get 13061 that are all of Jun2017 but I want only sales when OriginYear is the same year i filter.
Do you have an idea how to achieve it?
Thank you !!
The following measure should work:
OriginSales =
VAR SlicerYear =
SELECTEDVALUE ( CalendarTable[Year] )
RETURN
CALCULATE ( [TotSales], SalesTable[Origin Year] = SlicerYear )We create a variable called SlicerYear that stores the current value from the slicer.
We then modify the filter context by using CALCULATE(), and specify that we want SalesTable[OriginYear] to equal the stored SlicerYear value.
All the best,
~ Chris H
6 Replies
The following measure should work:
OriginSales =
VAR SlicerYear =
SELECTEDVALUE ( CalendarTable[Year] )
RETURN
CALCULATE ( [TotSales], SalesTable[Origin Year] = SlicerYear )We create a variable called SlicerYear that stores the current value from the slicer.
We then modify the filter context by using CALCULATE(), and specify that we want SalesTable[OriginYear] to equal the stored SlicerYear value.
All the best,
~ Chris H
Hi Anonymous do you know how can i replace SELECTEDVALUE for a valid function in a tabular model on SSAS ? Because it doesn't recognize SELECTEDVALUE as a valid function.
You must have an earlier version of SSAS, no worries.
Replace SELECTEDVALUE() with MIN(). It will accomplish the same thing; it will return the smallest year value in the current filter context.
If nothing is selected in the slicer, it will return the first year. If the slicer is set to Year = 2017, then the only value in the current filter context is 2017, so MIN() will return 2017.
Hi,
I do not get it. How did 13,061 comes about. Does number 4 represent January?
Hi Ashish -
I don't think that 2nd image actually corresponds to the number 13,061. I believe the OP meant that the filter context generated by his / her slicers was returning the larger box. However, the desired result is only a subset of that filter context, such that the Origin Year column matches the Year value from the slicer.
I believe my measure posted previously will accomplish this.
Hi Ashish_Mathur Anonymous
Sorry for mistake this is what i get when select Jan 2017
Finally i could get the value.
Thanks a lot !
