cancel
Showing results for
Did you mean:
Super User IV

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

Try

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

You may need all(sales) filter.

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 ,

You missed one thing. The dates in my sales table are not continuous, ie, it has gaps. But, in your case, you have a continuous date series. So, this formula won't work in my case.

Thanks
Shubs

Super User IV

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

But How any YTD OR MTD formula can work on the null Date?

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 ,

The problem is not null. I can filter it as there is only 1 record.
Main problem is that my dates are not continuous from 1 to 31. It has gaps in mostly all months.
That is why it is working fine when I get a Year and then a month. But, it fails when I use Month_Year column and doesn't give correct YTD/MTD results.

Super User IV

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

Ok, If all dates are not there at the sales table. This means you will not get all dates in the filter and can not get MTD and YTD. The only way out is date dim. Just check why is many to one from date to sales.

Or can You do. Create a copy of the file with only dates of sales and Date and dim. And if the issues are still there. Please share that file.

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 ,

Please find the sample file and provide me with YTD and MTD formula with this data.
https://www.dropbox.com/s/l1sazg29hz6qgqp/Test.xlsx?dl=0

Thanks
Shubs

Super User IV

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

There seems to be two issue, noncontinuous dates and dates have a timestamp.

I created two new formula and check value with excel seems fine me.

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

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

Created a pivot in excel for verification

Excel

Pbixfile

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 ,

When I use the year month column, everything is getting blank as per your formula.
The date has a timestamp, but I require only the date. So, I will transform it to date format. The main issue is that the formula should work when I filter the month-year slicer.

Super User IV

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

```MTD Sales =
Var _min_end_date = CALCULATE(MAXX(Dates,STARTOFMONTH(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

Member

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

Hi @amitchandak ,

This is the MTD calculation. I think this is now working.
Could you please help me with the YTD Calculations as well and then I can verify both of them together.

Announcements

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