Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

YTD for previous year

I need to make a year to date for the same period last year calculation. I want to present this year and previous year next to each other in a bar chart. The periods are business defined. I cannot use standard time-intelligence functions. I have created the next test measure because my original measure in the bar chart returns the same result for each period in the previous year. 

 

YTD Last Date Last Year =
VAR RemoveFilter =
    FILTER (
        ALL ( Date);
        Date[Year] <= YEAR ( VALUES ( Last Refresh'[Last Refresh] ) ) -1
            && Date[Date] <= MAX ( Date[Date] )
    )
VAR Last Date =
    CALCULATE ( LASTDATE ( Date[Date] ); RemoveFilter )
RETURN
    Last Date Last Year

 

My intention is that the test measure returns the last date of each period for the previous year. It didn't work that way though. It returns for each period the last day of the year (screenshot below). I've also made a measure in which I removed the '-1' in the formula. This measure does return the last date of the period for this year. How can I create the measure in such a way that it returns the last day of the period in the previous year?

 

 

 

Need Help

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I tried several different calculations, but nothing works. I 've given it a lot of time but i do not have the feeling that I really understand how the syntax works. I've applied the syntax in Time Intelligence "The Hard Way" in the next measure and tried to compute a first date, last date and row count on the date table in order to check if the calculation is correct.

       

      VAR MAX_Year=

      YEAR ( VALUES ( 'Last refresh'[Last refresh] ) )
      VAR MAX_Date=
      MAX ( Datum[Datum] )
      VAR TempTable =
      CALCULATETABLE ( Date; ALL ( Date[Date] ); ALL ( Date[Date] ) )
      VAR RemoveFilter =
      FILTER ( TempTable; Datum[Year] <= MAX_Year && Datum[Date] <= MAX_Date)
      RETURN
      CALCULATE ( FIRSTDATE ( Datum[Datum] ); RemoveFilter )
       
      Results into:

       

      The rowcount suggests that not all rows are counted in the datetable that are before the last date of each period in this or previous year (not really a ytd, but a count of all mutations over all years). What I want to achieve is to:

      1. Filter the date table with dates <= this year (and in the other measure <= previous year)
      2. Apply visual level filter (periods, weeks) (So the max date will only return the max date of a period,week in this year)
      3. Filter the date table to return all dates <= max date for each period,week 
      4. Calculate on facttable filtered on dates based on previous steps 

      Perhaps I need to find another approach, or is this possible? Because periods can vary in start- and enddate I cannot use standard time based calculations I guess.