Dynamic To date measures

[DataFab2023]

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

[amitchandak · Accepted solution]

@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