Forum Discussion

Scotty88's avatar
Scotty88
Frequent Visitor
4 years ago

Dynamic Time Period - YTD Using Dates in Period

Hello,   I am trying to create a forumla for my YTD Calculation Item in tabular editor where the date range for my selected measure is filtered to Year to Date by using Dates in Period and subtract...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

Scotty88 , In case of YTD data should increase for all dates ?

 

example

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE(selectedmeasure()  ,DATESBETWEEN('Date'[Date],_min,_max))

 

or only date filter

 

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE(selectedmeasure()  ,filter('Date', 'Date'[Date]>= _min && 'Date'[Date] <= _max))

Scotty88's avatar
Scotty88
Frequent Visitor
4 years ago

Hi amitchandak , In my case i do not want the YTD sales total to be cumulative but rather show the range of days for the calculated meeasure but the sales should only be for that particular day. 

 

I tried returning the second calculation above but it returned all of the dates in my date table. The calculation itself is correct, however, it does not filter the date table properly to show only the date range.