YTD calulation in reverse

[MeadowDec]

Hi,

I need to make a measure that works out the 'complete' YTD value for the current financial year April to March, and then deducts the YTD value for the current month from that.

I was using DATESYTD function for my YTD measure = ActualRevenue_YTD = CALCULATE([ActualRevenue],DATESYTD('Date'[Date], "3-31"))

I do not know how to stamp this figure as at March in any given year however to put into a variable to then deduct the current month from?

I found the 'Reverse Cumulative Sales' online (thanks to enterprisedna) =

VAR CumulativeTotal = CALCULATE([ActualRevenue], FILTER(ALLSELECTED('Date'), 'Date'[Date] >= MIN('Date'[Date])))

RETURN

IF(ISBLANK([ActualRevenue]), BLANK(), CumulativeTotal)

Which seems like a good start position for me as essentially it replicates the logic that the DATESYTD function performs only in reverse, except the calculation is continuous rather than resetting at year end (how to make this reset each new financial year start?)

Could anybody please tell me how to put the final month YTD value into a variable so that I could then deduct the current months YTD value from it?

Thank you greatly for your considerations

[Dangar332]

Hi, @MeadowDec 

measure =
var curryear = year(today())-1
return
CALCULATE([ActualRevenue],'Date'[Date]<=today() && 'Date'[Date]>=date(curryear,4,1))

[MeadowDec]

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!

[MeadowDec · Accepted solution]

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