Forum Discussion
Mixing evolution graphics with single time graphics.
Hello,
I have a PBI page where i want to show two types of graphics controlled by just one datetime slicer. One group shows the data for a specific month, for example, May. The other group of graphics will show the evolution of a different data for the last 12 months starting, in this case, on May.
I know how to do this using two slicers for the different groups, buy i want to use just one slicer.
I tried two different aproaches:
- On first place, I tried to not correlate the slicer with the evolution graphics through the "Edit interactions" option, and then setting the range of the X-axis with a function, but since the slicer is not related with the graph I can't create a function that changes the date range dynamically with the slicer.
- On second place, I tried to use ALL function on the meassures I want to use for my evolution graphics, but I only can get a single value calculated with all the date range, but not the evolution I want.
I don't know if someone faced this problem earlier, I think the easiest way would be to go the first way, I just need a way to make the functon I'm using for the X-axis to be affected by the slicer on the page even tho the visual she is on is not related to the slicer.
Thanks for your time reading, I hope i wasn't too short explaining myself.
Rafael314 , You need to have a second Date table. The second date table you can use on axis for Rolling 12 months
These measures you need to create , case on table is disconnected second case have a inactive join
//DateInd is an independent Date table, Date is joined with Table
new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return sumx(filter( VALUES(DateInd[Date]), DateInd[Date]>=_min && DateInd[Date] <= _max),
CALCULATE(Sumx(filter(VALUES('Date'[Date]), 'Date'[Date] = max(DateInd[Date])),[Net Sales Measure]),filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ))//DateInd is joined with indirect join, Date is joined with Table with Active join.
new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return
CALCULATE([Net Sales Measure],filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ,USERELATIONSHIP('DateInd'[Date], Sales[Sales Date]))
1 Reply
Rafael314 , You need to have a second Date table. The second date table you can use on axis for Rolling 12 months
These measures you need to create , case on table is disconnected second case have a inactive join
//DateInd is an independent Date table, Date is joined with Table
new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return sumx(filter( VALUES(DateInd[Date]), DateInd[Date]>=_min && DateInd[Date] <= _max),
CALCULATE(Sumx(filter(VALUES('Date'[Date]), 'Date'[Date] = max(DateInd[Date])),[Net Sales Measure]),filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ))//DateInd is joined with indirect join, Date is joined with Table with Active join.
new measure =
Var _max = Max('Date'[Date])
var _min = EOMONTH(_max, -12)+1
return
CALCULATE([Net Sales Measure],filter(ALL('Date'), 'Date'[Date]>=_min && 'Date'[Date]<=_max) ,USERELATIONSHIP('DateInd'[Date], Sales[Sales Date]))