YTD as KPI

[lweny]

Hi all, I have a requirement here which need me to create a YTD and MAT as KPI. YTD is counted from the beginning of the current year (Jan) until current month, and MAT is counted from current month to the last year of current month (for example now is May, so it will be counted from May 2019 to May 2018, total 12 months). This KPI will affect the result of total sales displayed. Anyone has an idea how to achieve this? And also, do I need to connect this KPI to the Sales dimension? Please help! Thank you in advance

[Vegar]

Assuming you want to calculate SUM(Amount) and that your date field is named Date and that you have other calendar fields such as YearMonth, Month, Week and Year.

YTD

=count({<
   YearMonth, 
   Month,  
   Week, 
   Year,
   Date={">=$(=YearStart(today())) <=$(=monthend(max(Date)))"}
>} Date)

MAT

=count({<
   YearMonth,
   Year,
   Month,
   Week, 
   Date={">$(=addyears(monthend(max(Date)),-1)) <=$(=monthend(max(Date)))"}
>} Date)

[lweny]

Hi Vegar, thanks so much for replying! Apologize I forgot to mention, here I have a field name Month which contain months from 1 to 12, and Type field which contains "2017 Actual, 2018 Target, etc". That's all no other Date fields

[lweny]

So, do you have another solution please?

[Vegar]

I would suggest you to create some kind of date field from the month and the first part of Type. It will be very helpful for your MAT calculation.

Maybe you can do something like this:

Load
...
makedate(Subfield(Type,' ', 1), Month) as YearMonthDate,
...
FROM...

[Sameer9585]

YTD:

Sum({<Year=,Month=,OrderDate ={"$(='>=' & Date(YearStart(Max(OrderDate)),'MM/DD/YYYY') & '<=' & Date(Max(OrderDate),'MM/DD/YYYY'))"}>}Sales)

This should work

[Vegar]

@Sameer9585: As I understand @lweny does not have a date field in his datamodel.

He will be able to do YTD calculation using the month field as long he is selecting the right type (containing year), but it is a bit trickier getting a rolling 12 value without some kind of yearMonth or date field.

Could you @lweny please provide some kind of sample data/application to help us understand your situation. Please let us know if it is possible to do changes in the data model or if you are limited to front end design/expressions.

[lweny]

Hi @Vegar: here I attached the sample data for your reference. Month and Year from Type field to sum the Sales Amount.

[Sameer9585]

Yeah provide any sort of document so that we can figure it out ##- Please
type your reply above this line -##

[Vegar]

Try the attached app.