13 Replies Latest reply on Jul 12, 2018 1:39 AM by Mahfooj Khan

Show MTD YTD with default on TODAY

Product

Sales (TODAY)
MTDYTD
Laptop2005001000
Keyboard5006002000
Mouse60010003000

Hi all

May I know how to come out the MTD and YTD when the view of MAX date/Today by default.

Referring to the table. Sales show the total sales in the latest date of the data source.

And MTD is the running sum of the sales for product that month to date. For eg, MTD, 500 means the aggregate sales for laptop during that month.

And same goes to YTD,  the running sum of the sales for product that year to date.

May I know how to do it?

Thanks

• 1. Re: Show MTD YTD with default on TODAY

Hi,

Find my approach below,

Sales TODAY:

IF [Date]={MAX([Date])} THEN [Sales] END

MTD:

IF DATETRUNC('month',[Date])>={MAX(DATETRUNC('month',[Date]))}

AND [Date]<={MAX([Date])} THEN [Sales]

END

YTD:

IF DATETRUNC('year',[Date])>={MAX(DATETRUNC('year',[Date]))}

AND [Date]<={MAX([Date])} THEN [Sales]

END

Mahfooj

1 of 1 people found this helpful
• 2. Re: Show MTD YTD with default on TODAY

Haan,

Pl attach some data But For MTD you Can Do This:

For YTD

{FIXED Product:SUM( IF YEAR(Your Date)=YEAR(TODAY())THEN Sales END)}

1 of 1 people found this helpful
• 3. Re: Show MTD YTD with default on TODAY

Hi Haan,

If you got what you need, then you can close Thread.

Thanks

Deepak

• 4. Re: Show MTD YTD with default on TODAY

Dear Mahfooj

I realized that I am not able to put " Sale TODAY, MTD and YTD in a row. But MTD and YTD can,

I think it is due to Sale TODAY is not a measure value....

May I know how to do it as shown in the table above?

Thanks

• 5. Re: Show MTD YTD with default on TODAY

Create a measure to calculate Sales TODAY. For that you can use the formula which I've mentioned in my previous reply. Then using measure names and measure values you can create a cross tab view from those measures.

If you're still unable to to get the expected view then kindly share some mock data in packaged workbook/excel.

Mahfooj

• 6. Re: Show MTD YTD with default on TODAY

Dear Deepak

When applying the calculation field, there are 2 errors which are

-An empty name is not allowed

-Expected closing parenthesis or comma while parsing argument list for DATE.

May i know what is the problem?

Thanks

• 7. Re: Show MTD YTD with default on TODAY

Dear Mahfooj

I am able to do it already...Thanks!

Refer to my attached workbook, may I know how to get the blank value to be changed to zero?

• 8. Re: Show MTD YTD with default on TODAY

Dear Mahfooj

What should i do if i want to add in a features to enable the user to search date? and the information for max date, mtd and ytd to change accordingly?

Thanks

• 9. Re: Show MTD YTD with default on TODAY

Hi,

In your IF..ELSE statements you've just include ELSE part with 0

Like this

IF [Date]={MAX([Date])} THEN [Amount] ELSE 0 END

Do the same in MTD calculation, you'll get this view.

Hope this help. If you got your expected result then you can close the thread by marking my answer as correct so that others can refer this post who have similar query.

Mahfooj

1 of 1 people found this helpful
• 10. Re: Show MTD YTD with default on TODAY

Dear Mahfooj

What should i do if i want to add in a features to enable the user to search date? and the information for max date, mtd and ytd to change accordingly?

Thanks

• 11. Re: Show MTD YTD with default on TODAY

You need create a date parameter for selecting any date and based on that you need to calculate the MTD /YTD

Let me know if you've any query.

Mahfooj

1 of 1 people found this helpful
• 12. Re: Show MTD YTD with default on TODAY

Dear Mahfooj

Thank you very much!!!!!!!!

• 13. Re: Show MTD YTD with default on TODAY

You're welcome