Forum Discussion
YTD calulation in reverse
The answer was I had to hard code in the day & month start & end dates while pulling the filter context relevant year from attributes living in the date dimension and build into a date function as Dangar332 demonstrated in his answer above.
YTG =
VAR MaxFinDate = max('Date'[FinancialYearEnd])
VAR MinFinDate = min('Date'[FinancialYearStart])
VAR FullYTD = CALCULATE([ActualRevenue],'Date'[Date] <= date(MaxFinDate,3,31) && 'Date'[Date] >= date(MinFinDate,4,1))
VAR Result = FullYTD - [ActualReleasedRevenue_YTD]
RETURN Result
Thanks Dangar332 for what appears to be a start at least to what I need, it's quite hard to even tell really, it is not easy wrapping your head around this dax stuff is it 😂 & all I seemed to hear before I started looking at dax is that it is a simple functional language!
Of course at the moment that calculation is fixed to the current year only, how would I turn that curryear variable into a relative date dimension calculation associated to the years/months of the matrix visual on my report for instance?
Thanks for the help!
The answer was I had to hard code in the day & month start & end dates while pulling the filter context relevant year from attributes living in the date dimension and build into a date function as Dangar332 demonstrated in his answer above.
YTG =
VAR MaxFinDate = max('Date'[FinancialYearEnd])
VAR MinFinDate = min('Date'[FinancialYearStart])
VAR FullYTD = CALCULATE([ActualRevenue],'Date'[Date] <= date(MaxFinDate,3,31) && 'Date'[Date] >= date(MinFinDate,4,1))
VAR Result = FullYTD - [ActualReleasedRevenue_YTD]
RETURN Result