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 Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper III

## 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 Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper III

## 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 Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper III

## 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 Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper III

## 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 Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Helper III

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

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors