Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

View solution in original post

22 REPLIES 22
amitchandak
Super User
Super User

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

Anonymous
Not applicable

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.

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

Anonymous
Not applicable

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

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.

Anonymous
Not applicable

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?

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

Anonymous
Not applicable

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

But How any YTD OR MTD formula can work on the null Date?

Anonymous
Not applicable

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?

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.

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

 

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

 

 

Anonymous
Not applicable

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.

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))
Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.