cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User IV
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.

Please check file https://www.dropbox.com/s/k4r0wlql0nzo6ib/CompareRange_withouttimedim.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 Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Shubs Helper III
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
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?





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


Shubs Helper III
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.

Can you please help me with it?

Super User IV
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.





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


Shubs Helper III
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
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 = 
Var _max_end_date = CALCULATE(MAXX(Dates,DATEADD(DATEADD(Dates[Date],-12,MONTH),1,DAY)),ALLSELECTED(Dates[Date]))
Var _min_end_date = CALCULATE(MAXX(Dates,STARTOFMONTH(DATEADD(Dates[Date],-12,MONTH))),ALLSELECTED(Dates[Date]))
Return
 CALCULATE(SUM(DATA[Amount]),Data[Date]>=_min_end_date && Data[Date] <_max_end_date)
 
 
 MTD Sales = 
Var _max_end_date = CALCULATE(MAXX(Dates,DATEADD(Dates[Date],1,DAY)),ALLSELECTED(Dates[Date]))
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





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


Shubs Helper III
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.

Image.PNG

 

Super User IV
Super User IV

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

add  crossfilter, like

 

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

 

 





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


Shubs Helper III
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.

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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