Forum Discussion
Prior Fiscal YTD Total
I did solve the problem one other way. I created a second date column in my time table YTD_Date_DT and it is populated for all dates from July 1 (start of the fiscal year) until yesterday and then is null after that.
I then summed my measure on that date
CALCULATE( SUM('DW Sales_Purchase_Fact'[Quantity_50LB]), 'DW Time_DIM'[YTD_Date_DT] )
so as long as I can accurately keep this column in the time table up to date, it should work fine.
also allows the user to select 2, 3, 4, 5, 6 years to compare.
I just figured this out this morning, but thanks for all your help. will try your solution as well.
this totals the whole year and not just the period from July 1st to Dec 6.
I want the total quantity for the same period each year.
the current year isn't a problem because it is an incomplete year but previous years are the issue
Community ChampionSorry I missed what you were trying to achieve. There mght be an easier way to achieve it but I think this would work: (You need a financial year column in your date table)
Period Compare =
VAR CurrentYear =
CALCULATE(
MAX('Date'[Financial Year]),
'Calendar'[Date] = TODAY()
)
VAR LatestVisibleDate = LASTDATE('Date'[Date])
VAR MaxYearInFilterContext =
CALCULATE(
MAX('Date'[Financial Year]),
LatestVisibleDate
)
VAR YearOffset = CurrentYear - MaxYearInFilterContext
VAR DatesThisYear =
CALCULATETABLE(
DATESYTD('Calendar'[Date], "31/06"),
'Calendar'[Date] <= TODAY()
)
VAR PreviousYearDates = DATEADD(DatesThisYear, YearOffset, YEAR)
VAR Result =
CALCULATE(
SUM('DW Sales_Purchase_Fact'[Quantity_50LB]),
PreviousYearDates
)
RETURN Resultwow no wonder I didn't just figure this out on my own.
thanks
- bcdobbs
I'm hoping someone has a more elegant solution but it's something I've always had to fight to make work!