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


Shubs Member
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
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!
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


Shubs Member
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.

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


Shubs Member
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
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!
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


Shubs Member
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.

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


Shubs Member
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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors