- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- Create MTD & YTD Calculation In My Model

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

Create MTD & YTD Calculation In My Model

peersbrennanctb

Participant

- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content

on 2023 Sep 20 4:29 PM

612

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

Active Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

2023 Sep 20
5:26 PM

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

Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

2023 Sep 20
4:57 PM

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

Active Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content

2023 Sep 20
5:28 PM

Ask a Question

Related Content

- SAP Build Extensibility Wizard for SAP S/4HANA Cloud Public Edition in Technology Blogs by SAP
- ALMathon 2024 for SAP Cloud ALM in Technology Blogs by SAP
- Upload Plan data using a ‘Starter Button’ in SAC Story from a File (Excel/CSV) in Technology Blogs by Members
- Understanding CQRS with a Practical Real-World Example in Technology Blogs by SAP
- SAC Calculate after aggregation based on datasphere in Technology Q&A

Top Q&A Solution Author

User | Count |
---|---|

67 | |

8 | |

8 | |

6 | |

6 | |

6 | |

6 | |

6 | |

5 | |

5 |

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.