Create MTD & YTD Calculation In My Model

[peersbrennanctb]

Hi

I am looking how to create MTD calculation and YTD calculation in my model for invoice sales. I know how to achieve this in the story but I want to create in my model so I don't need to create each time in a story.

Thanks

[N1kh1l]

peersbrennanctb

The below should work for YTD. [GROSSAMOUNT] is the original measure for which YTD will be calculated. Till now I have used this for model level YTD calculation.

ITERATE(IF([d/Date].[p/MONTHDESC]="Jan" ,0 , PRIOR () ) + [GROSSAMOUNT], [GROSSAMOUNT], [d/Date].[p/CALMONTH])

For MTD and even YTD you can use the newly introduced ToPeriod dynamic time calculation at model level in recent QRC release. Refer to the SAP Note – 3365184 also for details.

https://blogs.sap.com/2023/09/14/whats-new-in-sap-analytics-cloud-release-2023.19/

YTD

 LOOKUP([GROSSAMOUNT] ,[d/CREATEDAT]=ToPeriod("Year" ) )

MTD

LOOKUP([GROSSAMOUNT] ,[d/CREATEDAT]=ToPeriod("Month" ) )

I ran some test as below

Hope this helps !!

Br.

Nikhil

[vishalakshmi]

Hello Peers,

Try the below steps:

MTD Calculation:

a. In your model, go to the "Models" tab.

b. Click on "Edit Model."

c. In the "Measures" section, click on "Add Measure."

d. Name your measure (e.g., "MTD Sales").

e. In the formula section, use a formula similar to the following:

SUM([Sales]) WHERE ([Date] >= FIRSTDAYOFMONTH(TODAY()) AND [Date] <= TODAY())

This formula calculates the sum of sales for the current month.

YTD Calculation:

a. In your model, go to the "Models" tab.

b. Click on "Edit Model."

c. In the "Measures" section, click on "Add Measure."

d. Name your measure (e.g., "YTD Sales").

e. In the formula section, use a formula similar to the following:

SUM([Sales]) WHERE ([Date] >= FIRSTDAYOFYEAR(TODAY()) AND [Date] <= TODAY())

This formula calculates the sum of sales for the current year.

Save the model and use it in stories.

Thanks,

Lakshmi.

[N1kh1l]

vishalakshmi.namuduri

Looks like DAX syntax which of course does not work in SAC 😞