MTD YTD Calculation for Monthly Basis Data (Please Help URGENT)

[Anonymous]

Hi All,

Kindly need your help to solve my report Problem. Currently I am working with monthly basis data, so the data time is only by month not day/date. However, i need to show MTD and YTD data on the visualization.

1. Could you please help explain how to create the measure?

2. Can i use slicer with range for month but still show the MTD & YTD on the visual?

Below is the example of month data

Month
1
2
3
4
5
6
7
8
9
10
11
12

Urgent help is highly appreciated. Thank you

Best regards

[niteshtrehan89]

Hi @Firman_Wijaya,

need to create a date table

date(year,month,1) 

or other way is creating a calculated column

column= "1/" & month & "/2020" and then change the datatype to date.

then calculate the YTD and MTD 

YTD= TotalYTD(sum(sales),column)

MTD=TotalMTD(sum(sales),column)

[amitchandak · Accepted solution]

@Anonymous , Do you have a year also?

One way is create a date and use time intelligence

Date = date([year], [month],1)

With help from date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Other wise , create a separate table with Year and Month and create column YYYY MM

Year Month = [Year]*100 + [Month]

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Month] <= Max('Date'[Month]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Month] <= Max('Date'[Month])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.