CODEWITHSUNDEEP
tableau-api
Karishma Dudhbade
Karishma Dudhbade

Reputation: 611

how to calculate YTD and MTD calculations

I have a year drop down with YTD and MTD, field named datavalue_1 and month_name, and parameter called Month_parameter. So when I select YTD from the drop down,It should display sum of datavalue_1 where [month_name]<=[Month_parameter] and when I select MTD from the drop down, it should display sum of datavalue_1 of that month. What I mean is, if September is selected then YTD should return the sum of datavalue_1 from January to September, and if MTD is selected then it should return sum of datavalue_1 of September.

How do I write the calculations for this. Thanks in advance!!! Regards

Upvotes: 1

Views: 14468

Answers (1)

Inox
Inox

Reputation: 2275

Okay, you're in the right direction, just need some fine tuning. First, if you have data from many years, you'll be adding wrong stuff. You need also check if the year is the same of the date you want to analyze. So you need a [Year Parameter]

Instead of just month([Sales Date]) <=[Month Parameter], you should try

IF month([Sales Date]) <=[Month Parameter] AND YEAR([Sales Date]) <=[Year Parameter]
THEN [datavalue_1]
END

Do the same with MTD calculation (I'll call them MTD and YTD fields from now on), with a Day Parameter

Now, you just need a simple if else statement. Create a paramenter called [Metric], that can be either MTD or YTD. Then

IF [Metric] = 'MTD'
THEN [MTD]
ELSEIF [Metric] = 'YTD'
THEN [YTD]
END

Note that this is not the best way to go around this situation. You can use a running calculation field (that is a table calculation) to calculate MTD or YTD values. Just need to adjust the right parameters (resetting every month or every year)

Upvotes: 2

Related Questions