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 us...
Need Help
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.