Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
successfully added MTD but trying to add YTD to same Pivot Table.
Currently have not found a viable solution
Measure:
SUM(
{<[EFFECTIVEDATE.autoCalendar.InYTD]={"$(=YEAR(TODAY())-1)","$(=YEAR(TODAY()))"}
,MONTH
,WEEK
,DATE={"=SETDATEYEAR(DATE,YEAR(TODAY()))>=YEARSTART(TODAY()) AND SETDATEYEAR(DATE,YEAR(TODAY()))<TODAY()"}
>}
NORMALBALANCE)
added YTD flag in calendar script
If( $1 - YearStart($1) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD,
Tried max date - max date of previous year
MTD set as single measure:
sum(NORMALBALANCE) - AFTER(SUM(NORMALBALANCE))
want to see
for every month on current year, comparison of month over month and YTD comparison
Many thanks
Separated Yr-Mth into two dimensions, one for Year and one for Month.
For MTD, used After() for comparison
For YTD, used Last() for comparison
Not sure I am clear as to what you are looking to do. Would you be able to share some sample data with the expected output based on the sample shared/
@sunny_talwar et. al
A little more background as I am finding:
Only month-end dates are available.
Yr and Mth are individual dimensions [EFFECTIVEDATE.autoCalendar.Year] & [EFFECTIVEDATE.autoCalendar.Month].
I have made a Master Calendar and As Of Table
In sample file attached, the ytd - NOW columns are calculated with the below, but I have not gotten any further. Several solutions I have tried that I have read of your solutions or others, I can get the values individually, but when subtracting from one another, I get nulls. That is even when including year=,quarter=,month=,date= or using yearmonth, year, month for the effectivedate field as a dimension.
=ABOVE(Sum({<EFFECTIVEDATE={"=SetDateYear(EFFECTIVEDATE, Year(Today())) >= YearStart(Today(),0,12) and SetDateYear(EFFECTIVEDATE, Year(Today())) <= Today()"}>} NORMALBALANCE))
-
sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}NORMALBALANCE)
Below i added sample data as requested. Do you have an idea on how to display this across all months of current year against max date of last year. Then the same for all of last year against previous year?
Based on the data we have, there is only month-end data per year-month which does make it difficult in expressions that ask a specific date per month.
I have not been able to find a single formula that will help outside of performing and loading manually.
Thank you in advance
Separated Yr-Mth into two dimensions, one for Year and one for Month.
For MTD, used After() for comparison
For YTD, used Last() for comparison