How to Create YTD Gwt and P3 & P6 Ave

[Kenji_Masicat]

Hi,

I'm trying to compute for YTD gwt. My Order Date format is DD/MM/YYYY.

Also, how can I create measures for P3 months & P6 months?

Thanks a lot.

Kenji

[bill_mtc]

Just my two cents...

Create variables that would handle the maxYear, maxYearMonthNum, minlast3monthYearMonthNum

i.e variable value

for maximumYear = 2021

for maximumYearMonthNum = 202106

for minlast3monthYearMonthNum = 202104

Sample Data Set

So having the two tables loaded, Calendar will use the "Date" column to connect/communicate with the Transaction's "Date" column.

Sample Report and Sample Formula

YTD = Sum({<Year={maxYear}>} Amount)

Prev 3 months = Sum({<YearMonthNum={>minlast3monthYearMonthNum<=maxYearMonthNum}>} Amount)

It will always depends on the created data model, sometimes the simpler the data model the easier to create formula. Though, it is also case to case basis because the data model will always be based on the business process.