PYTD vs YTD Comparison , while Not Showing Dates
I have a request which i cannot seem to get working correctly without showing dates. I want to compare suppliers YTD spend vs PYTD spend, so only dates up to today for this year and last. I've have used the difference field to no avail and also a cumulative measure of my Net Spend Measure however it only seems to work year or date, these will always be fixed as this year and last.
Thanks in Advance.
2 replies
-
Hi
Is the PYTD from the beginning of last year up to the same date as today but last year? Or up to Today's date?
If the former then these two code snippets should work, just substitute your Measure and Date Dimension names:
YTD:
SUM(
YTD(
StrToMember(
[Data].[Date],
Date()
)
),
[measures].[Data Sales]
)PYTD (There are other ways of doing this as well):
SUM(
YTD(
StrToMember(
[Data].[Date],
AddDays(
Date(),
-365
)
)
),
[measures].[Data Sales]
)Date() returns today's date; StrToMember matches today's date with the dates in your Date dimension to select the Date Member for today; YTD returns a list of dates form 1st January to Today's Dates, SUM adds them all up for the Sales measure.
For last year, just use the AddDays*) function to subtract 365 days from today's date before matching it with the Date dimension.
Hope that helps.
Ian