Pivot table display MTD & YTD

[SonOfJeffGoldblum]

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

[sunny_talwar]

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/

[SonOfJeffGoldblum]

@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)

[SonOfJeffGoldblum]

@sunny_talwar 

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

[SonOfJeffGoldblum · Accepted solution]

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