cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Shubs Member
Member

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

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

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

Yes, I have marked it as a date table only

Shubs Member
Member

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

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
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

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