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 subtracting the count of days from my max sales day. When executed, there are multiple dates at the beginning of the year that do not return data at all even though there should be sales data for those dates and rather jumps back to last year. I have created the same measure hardcoding in the date YTD that needs to be subtracted (144 for today) and it works. I do not want to use DatesYTD or TotalYTD in my acutal calculation becuase I do not want the sales data to bee cumulative. Thank you for any help!

 

VAR Isdatesfiltered = CALCULATE( ISFILTERED( 'Date'[Date] ), ALLSELECTED( ) )
VAR LastDateDate = CALCULATE( MAX( 'Date'[Date] ), REMOVEFILTERS( ) )
VAR DaysYTD = CALCULATE( DISTINCTCOUNT( 'Date'[Date] ), DATESYTD( 'Date'[Date]) )
RETURN
IF(
Isdatesfiltered,
SELECTEDMEASURE(),
CALCULATE( SELECTEDMEASURE(), KEEPFILTERS( DATESINPERIOD( 'Date'[Date], LastDateDate, - DaysYTD, DAY ) ) )
)

 

Example 1: Using Formula for YTD

 

Example 2: Hard Coded Date  (144 Days)

 

Need Help

2 Replies

  • 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.