cancel
Showing results for
Did you mean:
Highlighted
Member

## YTD MTD Calculations which need to show results when filter with Month-Year

Hi All,

I have created the YTD and MTD calculations with my data which contains a table with all the required columns(including date column)
My formula is as follow:

MTD=CALCULATE([Amount],DATESMTD('Table'[Date]))
YTD=CALCULATE([Amount],DATESYTD('Table'[Date]))

Now I add two filters in my report- Year filter and Month filter.

The code is working fine with these filter, but my client doesn't want these two filter instead he want one filter(Month_Year).
I have created the Month_Year filter as well

Month_Year = FORMAT('Table'[Date],"MMM") & "-" & YEAR('Table'[Date])

But as this will be in text format, so when I select a value from it, my YTD/MTD measure doesn't show the correct value.

How I can rectify this and get correct values while filtering the data with Month_Year filter?

Thanks
Shubs
1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

Check

``` Last Year YTD Sales =
Return
CALCULATE(SUM(DATA[Amount]),Data[Date]>=_min_end_date && Data[Date] <_max_end_date,CROSSFILTER(Data[Date],Dates[Date],None))

YTD Sales =
Var _min_end_date = CALCULATE(MAXX(Dates,STARTOFYEAR(Dates[Date])),ALLSELECTED(Dates[Date]))
Return
CALCULATE(SUM(DATA[Amount]),Data[Date]>=_min_end_date && Data[Date] <_max_end_date,CROSSFILTER(Data[Date],Dates[Date],None))```

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

22 REPLIES 22
Super User IV

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User IV

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

I tried the folmula as follows for MTD and they are working fine with MMM-YYYY date filter. Last year formula is bit different

```MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer]))
Last Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date Filer],-12,MONTH))) ```

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Member

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

Hi @amitchandak ,

My Sales table has a date column in it and I don't have the separate date table. Could you please help me with how to create a date table and then see whether it will work or not?
Also, please keep in mind that I will be having both YTD and MTD measures on the same table. Both have to be filtered correctly when I filter from the Format Month column.

@Ashish_Mathur : Thanks for the reply. But, I can't share the data. I am working with single sales table that has all the details including date column.

Super User IV

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

You can use this code

```Dates =
VAR BaseCalendar =
CALENDARAUTO()
RETURN
GENERATE (
BaseCalendar,
VAR BaseDate = [Date]
VAR YearDate = YEAR ( BaseDate )
VAR MonthNumber = MONTH ( BaseDate )
RETRUN ROW (
"Day", BaseDate,
"Year", YearDate,
"Month Number", MonthNumber,
"Month", FORMAT ( BaseDate, "mmm" ),
"Year Month", FORMAT ( BaseDate, "mmm yy" )
)
)```

Also, please find my file. This you can use to test your calc and provide the sample if YTD do not work.

Use Page 6

https://www.dropbox.com/s/unfqa025ca7p3vw/CompareRange_timedim.pbix?dl=0

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Member

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

Hi @amitchandak ,

My Sales table doesn't have the only cumulative sale of a single day in one row. I have multiple rows of sales for a single day.
I have created the date table as per your provided code and then make a many to one relationship between my sales table and the date table. After adjusting the code and using the date from date table, everything turns to null.

Can you provide some other solution as per my requirement?
Or is there a way that I don't have to create a data table and instead just use date from sales table only to get the correct values?

Thanks
Shubs

Super User IV

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

Have you marked the date table as Date in the Model view? Right-click mark as a date table. The calculation differs a bit when a table is a date table.

I do not think MTD sales have anything to do with one record per day.

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Member

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

Yes, I have marked it as a date table only

Member

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

May be its on the relationship. I have a many to one relationship with the date table and the sales table

Member

## Re: YTD MTD Calculations which need to show results when filter with Month-Year

Hi @amitchandak ,

Is there a way that I can do this without creating a date table?
And using the date from the sales table only?
Though, as the dates in the sales tables have some null values and is not continuous. Thus it can't be converted to date table.

Can you help me with this?

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!