9 Replies Latest reply on Oct 5, 2016 6:45 PM by Shinichiro Murakami

# MTD & YTD calculation

Attached is the twbx. I have couple questions/issues.

I want to create MTD & YTD columns for each Measure.

YTD - Year to Date should show sum of all month based on 'Report Year' selected in filter

MTD - Month to Date should show data only for the selected 'Report Month'

Eg. I need 'YTD Store Sales MB' & 'MTD Store Sales MB'. If 'Report Year' = 2016 and 'Report Month' selected = 6 then

'YTD Store Sales MB' = SUM of all months of 2016 (ignore the month filter)   (if they select prior years e.g 2015 then sum of all months Jan to Dec)

'MTD Store Sales MB' = 'Store Sales MB' for just 6th month for 2016 (Take into consideration selected 'Report Month' & 'Report Year' in the quick filters.

There is another measure for which I need AVG - 'YTD Turnaround %' = AVG of all months of 2016 (ignore the month filter)

'MTD Turnaround %' = actual 'Turnaround %' for that month and year

User can select only one year and month from filter(single value)

• ###### 1. Re: MTD & YTD calculation

If the filtered range is different, the calculation does not work.

Only the way to make them work is  create each measure fields, bun in your case, you have so many measure fields already and not feasible.

My proposal is create two different workbook and combine them on dashboard.

[Year Filter]   //  in the sheet Year

[Report Year]=[Year Param]

[YearMonth Filter] //  in Mont sheet

[Report Year]=[Year Param] and [Report Month]=[Month Param]

Thanks,

Shin

• ###### 2. Re: MTD & YTD calculation

Shin,

My users want to see both MTD and YTD next to each other and not separately.

I can reduce the number of measures if needed but how can I achieve getting MTD and YTD next to each other?

• ###### 3. Re: MTD & YTD calculation

Not very fancy way, but just pivot column and row.

And remove all total/Sub total

Thanks,

Shin

• ###### 4. Re: MTD & YTD calculation

Hi Shin,

I dont think this will work for me for couple of reasons.

1. I have many store sites(around 40)

2. I have around 6 to 7 KPI measures

3. loose sorting capability across the row in different sheets.

Cant I somehow create parameter for 'Report Month' and then create separate calculated fields for each measure to create MTD and filter data for only that month?

• ###### 5. Re: MTD & YTD calculation

If you create all the KPI x 2(each for Month and Year), you should have chance.

Thanks,

Shin

• ###### 6. Re: MTD & YTD calculation

Shin, can you help creating one MTD & YTD based on the data I gave you?

• ###### 7. Re: MTD & YTD calculation

[Store Sales MB MTD]

if [Report Month]=[Param.Report Month] and [Report Year]=[Param.Report Year]

then [Store Sales MB] end

[Store Sales MB YTD]

if [Report Year]=[Param.Report Year]

then [Store Sales MB] end

Above two x number of LPI

three fields as example

Thanks,

Shin

• ###### 8. Re: MTD & YTD calculation

So far I am trying you first Yearly and Monthly approach. Top and bottom placement.

In the yearly sheet i have mutliple KPI where I am calculating yearly numbers.

Only for 1 KPI I dont want to include current month data in calculation.

Shin I am using you earlier formula which you recommended.

[Filter]

then "Hide" else "Show" end

Question is how do I apply this to only 1 KPI when I have mutlipe KPI's displayed in the yearly sheet.

e.g apply this filter only to 'Coffee Sales MB' in the earlier attached workbook.

• ###### 9. Re: MTD & YTD calculation

Change the formula diretly

[Store Sales MB Filtered]

then 0 else [Store Sales MB] end

<<Because it's already october, I modified the formula using "-2" of excluding latest 2 month to veridate>>

And 'Coffee Sales MB' does nohave data on eptember and I used  [Store Sales MB] instead].

Thanks,

Shin