cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User IV
Super User IV

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

Check

 Last Year YTD 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,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


View solution in original post

Shubs Helper III
Helper III

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

Hi @amitchandak ,

Thanks for the formulas. All calculations are working fine now.

Only one issue left, as the table is single filter relationship and values will work with the Month(column) of the Date table.

So the other columns like type, descriptions(the filters I am using in the page) won't be filtered when I the month year column.

I have edited the interaction so that I can see all the values. But, I want when I filter to Sept 19 then the type filter and other filters also show just the values that are in Sept 19.

Thanks
Shubs

Super User IV
Super User IV

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

Make your relation bidirectional. Edit relation has an option. But this might slow down performance.





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


Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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