cancel
Showing results for
Did you mean:
Helper III

## 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 =
Var _min_end_date = CALCULATE(MAXX(Dates,STARTOFYEAR(DATEADD(Dates[Date],-12,MONTH))),ALLSELECTED(Dates[Date]))
Return
CALCULATE(SUM(DATA[Amount]),Data[Date]>=_min_end_date && Data[Date] <_max_end_date,CROSSFILTER(Data[Date],Dates[Date],None))

YTD Sales =
Var _max_end_date = CALCULATE(MAXX(Dates,DATEADD(Dates[Date],1,DAY)),ALLSELECTED(Dates[Date]))
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))```

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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))) ```

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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

Helper III

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

Hi @amitchandak ,

Thanks for your response.

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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

Highlighted
Helper III

## 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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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

Helper III

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

Yes, I have marked it as a date table only

Helper III

## 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

Helper III

## 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

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors