Forum Discussion
Dynamic To date measures
Hi guys,
I have a set of measures that need to be calculated to date (based on the slicer)
For example: First day of the dataset is 01/01/2017 so this date will always be the start date and the "to date" has to be from the slicer.
If the user selects 01/01/2023 I need the measure to be calculated from 01/01/2017 until 01/01/2023
Let's say I have [Revenue] which is a simple measure and I need the revenue to be based always from the start of the period until the selected date in the slicer.
I also have a dimdate table that is connected to the OccuredDate in my Fact table where the revenue is.
How can I achieve this?
Many thanks
DataFab2023 , if you just want to show data, not trend
New measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(all(Date), Date[Date])
return
calculate( sum(Table[Value]), filter(all('Date') , 'Date'[Date] >=_min && 'Date'[Date] <=_max))In case you need trend the slicer need to be on independent table
//Date1 is an independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(all(Date1), Date1[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
1 Reply
DataFab2023 , if you just want to show data, not trend
New measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = minx(all(Date), Date[Date])
return
calculate( sum(Table[Value]), filter(all('Date') , 'Date'[Date] >=_min && 'Date'[Date] <=_max))In case you need trend the slicer need to be on independent table
//Date1 is an independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(all(Date1), Date1[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI