topic Pivot table display MTD & YTD in App Development

Pivot table display MTD & YTD

SonOfJeffGoldblum — Sat, 16 Nov 2024 01:22:01 GMT

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

Re: Pivot table display MTD & YTD

sunny_talwar — Tue, 29 Sep 2020 15:24:17 GMT

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/

Re: Pivot table display MTD & YTD

SonOfJeffGoldblum — Tue, 29 Sep 2020 21:04:01 GMT

Re: Pivot table display MTD & YTD

SonOfJeffGoldblum — Thu, 01 Oct 2020 14:49:20 GMT

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

Re: Pivot table display MTD & YTD

SonOfJeffGoldblum — Thu, 08 Oct 2020 15:31:24 GMT

@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

Re: Pivot table display MTD & YTD

SonOfJeffGoldblum — Fri, 16 Oct 2020 16:25:58 GMT

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