cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shubs Helper III
Helper III

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

Hi All,

I have created the YTD and MTD calculations with my data which contains a table with all the required columns(including date column)
My formula is as follow:

MTD=CALCULATE([Amount],DATESMTD('Table'[Date]))
MTD LY=CALCULATE([MTD], DATEADD('Table'[Date].[Date],-12,MONTH))
YTD=CALCULATE([Amount],DATESYTD('Table'[Date]))
YTD LY=CALCULATE([YTD], DATEADD('Table'[Date].[Date],-12,MONTH))

Now I add two filters in my report- Year filter and Month filter.

The code is working fine with these filter, but my client doesn't want these two filter instead he want one filter(Month_Year).
I have created the Month_Year filter as well

Month_Year = FORMAT('Table'[Date],"MMM") & "-" & YEAR('Table'[Date])

But as this will be in text format, so when I select a value from it, my YTD/MTD measure doesn't show the correct value.

How I can rectify this and get correct values while filtering the data with Month_Year filter?
 
Thanks
Shubs
1 ACCEPTED SOLUTION

Accepted Solutions
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

22 REPLIES 22
Super User IV
Super User IV

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

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User IV
Super User IV

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

I tried the folmula as follows for MTD and they are working fine with MMM-YYYY date filter. Last year formula is bit different 

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

Screenshot 2019-09-14 15.04.25.png





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


Highlighted
Shubs Helper III
Helper III

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

Hi @amitchandak ,

Thanks for your response.

My Sales table has a date column in it and I don't have the separate date table. Could you please help me with how to create a date table and then see whether it will work or not?
Also, please keep in mind that I will be having both YTD and MTD measures on the same table. Both have to be filtered correctly when I filter from the Format Month column.

@Ashish_Mathur : Thanks for the reply. But, I can't share the data. I am working with single sales table that has all the details including date column.

Super User IV
Super User IV

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

You can use this code

 

Dates =
VAR BaseCalendar =
    CALENDARAUTO()
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        RETRUN ROW (
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT ( BaseDate, "mmm" ),
            "Year Month", FORMAT ( BaseDate, "mmm yy" )
        )
    )

 

 

Refer https://community.powerbi.com/t5/Desktop/Efficient-Date-Time-Table-Creation-Using-DAX/td-p/466987

 

Also, please find my file. This you can use to test your calc and provide the sample if YTD do not work.

Use Page 6

https://www.dropbox.com/s/unfqa025ca7p3vw/CompareRange_timedim.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 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 Helper III
Helper III

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

Hi @amitchandak ,

My Sales table doesn't have the only cumulative sale of a single day in one row. I have multiple rows of sales for a single day.
I have created the date table as per your provided code and then make a many to one relationship between my sales table and the date table. After adjusting the code and using the date from date table, everything turns to null.

Can you provide some other solution as per my requirement?
Or is there a way that I don't have to create a data table and instead just use date from sales table only to get the correct values?

Thanks
Shubs

Super User IV
Super User IV

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

Have you marked the date table as Date in the Model view? Right-click mark as a date table. The calculation differs a bit when a table is a date table.

I do not think MTD sales have anything to do with one record per day.





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


Shubs Helper III
Helper III

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

Yes, I have marked it as a date table only

Shubs Helper III
Helper III

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

May be its on the relationship. I have a many to one relationship with the date table and the sales table

Shubs Helper III
Helper III

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

Hi @amitchandak ,

Is there a way that I can do this without creating a date table?
And using the date from the sales table only?
Though, as the dates in the sales tables have some null values and is not continuous. Thus it can't be converted to date table.

Can you help me with this?

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