Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I am looking to calculate Current Month-to-Date and Week-to-Date, as well as compare it to Previous Month-To-Date and Previous Week-to-Date. I already have the Year-to-Date calculations that are correct, but I'm not so sure on the MTD and the WTD ones:
Current YTD Sales = CALCULATE(sum(Sales[Sales]),YEAR(Sales[Created Date])=YEAR(TODAY()))
Current MTD Sales = CALCULATE(sum(Sales[Sales]),MONTH(Sales[Created Date])=MONTH(TODAY()))
(Not sure if this is correct, since there a multiple years in the data. It could be pulling in the same month from not just this year but from the other years)
Current WTD Sales = CALCULATE(sum(Sales[Sales]),WEEKNUM(Sales[Created Date])=WEEKNUM(TODAY()))
(will this calculate accurately, with Monday or Sunday being the start of the week?)
Previous YTD Sales = CALCULATE(
sum(Sales[Sales]),YEAR(Sales[Created Date])=YEAR(TODAY())-1,
FILTER(ALL(Sales[Created Date]),
'Sales'[Created Date]<=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))
)
Previous MTD Sales = CALCULATE(
sum(Sales[Sales]),MONTH(Sales[Created Date])=MONTH(TODAY())-1,
FILTER(ALL(Sales[Created Date]),
'Sales'[Created Date]<=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())))
)
Previous WTD Sales = CALCULATE(
sum(Sales[Sales]),WEEKNUM(Sales[Created Date])=WEEKNUM(TODAY())-1,
FILTER(ALL(Sales[Created Date]),
'Sales'[Created Date]<=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-6))
)
(not sure about the Previous WTD Sales is accurate either)
Can someone please let me know if all these formulas look accurate?
Solved! Go to Solution.
Hi Rsanjuan,
I think the formula for month and week won’t work here, and as you concerned, the formula can’t identify which year the month belongs to, also for the week.
For the Previous sales, the formula posted might not be the correct one, see the testing results, the formula used are all copied from the posted ones:
Which I think should be the correct one is the following:
SalesYTD =
TOTALYTD (
[TotalSales],
'Date'[Date]
)
And:
SalesLastYear =
CALCULATE([TotalSales], DATEADD(DATESYTD('Date'[Date]),-1,Year))
Those two formula need a date table to work with.
For month total and Week total, see:
"
Iso MTD :=
IF (
HASONEVALUE ( Dates[ISO Year] )
&& HASONEVALUE (Dates[ISO Month Number] ),
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER (
ALL ( Dates ),
Dates[ISO Year] = VALUES ( Dates[ISO Year] )
&& Dates[ISO Month Number] = VALUES ( Dates[ISO Month Number] )
&& Dates[Date] <= MAX ( Dates[Date] )
)
),
BLANK ()
)
Iso WTD :=
IF (
HASONEVALUE ( Dates[ISO Year] )
&& HASONEVALUE (Dates[ISO Week Number] ),
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER (
ALL ( Dates ),
Dates[ISO Year] = VALUES ( Dates[ISO Year] )
&& Dates[ISO Week Number] = VALUES ( Dates[ISO Week Number] )
&& Dates[Date] <= MAX ( Dates[Date] )
)
),
BLANK ()
)
"
Check details in the the article below:
Week-Based Time Intelligence in DAX
Before using the formula posted in the article, we need to create a date table containing the following columns:
Year, month and week number in a year.
New table function to create a datetable;
Datatable = calendar(MinDate, MaxDate);
Then add the following columns:
Year = year(Datetable[Date])
Month = month (Datetable[Date])
Weeknum = WeekNum(Datetable[Date])
After that, follow the formula mentioned in the blog to generate the YTD, MTD and WTD function.
If any further help needed, please post back.
Regards
how can ı start to week first day in monday
Current WTD Sales = CALCULATE(sum(Sales[Sales]),WEEKNUM(Sales[Created Date])=WEEKNUM(TODAY()))
(will this calculate accurately, with Monday or Sunday being the start of the week?)
Hello,
I have a situation where I need to calculate the WTD and MTD, QTD and YTD for my sales facts and comparing it with respective previous year numbers. But in my date dimension, the business fiscal year starts on 4th wednesday in March and also 8 of my months end with 28 days and 4 months end with 35 days.
The problem here is that, a perticular month in one fiscal year have different dates in the next year and a perticular fiscal week have different dates in two different years. By which if i calculate with month number and week number then my comparisions are not for the same set of dates.
Can someone please help me on calculating the facts based on the current year dates to the previous year.
Fiscal_Year Fiscal_Year_StartDate Fiscal_Year_EndDate Fiscal_Quarter_Number Fiscal_Quarter_Name Fiscal_Quarter_StartDate Fiscal_Quarter_EndDate Fiscal_Period_Number Fiscal_Period_Name Fiscal_Period_StartDate Fiscal_Period_EndDate
2020 | 4/25/2019 12:00:00 AM | 4/22/2020 12:00:00 AM | 4 | Quarter 4 | 1/23/2020 12:00:00 AM | 4/22/2020 12:00:00 AM | 11 | Period 11 | 2/20/2020 12:00:00 AM | 3/18/2020 12:00:00 AM |
2020 | 4/25/2019 12:00:00 AM | 4/22/2020 12:00:00 AM | 4 | Quarter 4 | 1/23/2020 12:00:00 AM | 4/22/2020 12:00:00 AM | 11 | Period 11 | 2/20/2020 12:00:00 AM | 3/18/2020 12:00:00 AM |
2020 | 4/25/2019 12:00:00 AM | 4/22/2020 12:00:00 AM | 4 | Quarter 4 | 1/23/2020 12:00:00 AM | 4/22/2020 12:00:00 AM | 11 | Period 11 | 2/20/2020 12:00:00 AM | 3/18/2020 12:00:00 AM |
2019 | 4/26/2018 12:00:00 AM | 4/24/2019 12:00:00 AM | 4 | Quarter 4 | 1/24/2019 12:00:00 AM | 4/24/2019 12:00:00 AM | 11 | Period 11 | 2/21/2019 12:00:00 AM | 3/20/2019 12:00:00 AM |
2019 | 4/26/2018 12:00:00 AM | 4/24/2019 12:00:00 AM | 4 | Quarter 4 | 1/24/2019 12:00:00 AM | 4/24/2019 12:00:00 AM | 11 | Period 11 | 2/21/2019 12:00:00 AM | 3/20/2019 12:00:00 AM |
2018 | 4/27/2017 12:00:00 AM | 4/25/2018 12:00:00 AM | 4 | Quarter 4 | 1/25/2018 12:00:00 AM | 4/25/2018 12:00:00 AM | 11 | Period 11 | 2/22/2018 12:00:00 AM | 3/21/2018 12:00:00 AM |
2018 | 4/27/2017 12:00:00 AM | 4/25/2018 12:00:00 AM | 4 | Quarter 4 | 1/25/2018 12:00:00 AM | 4/25/2018 12:00:00 AM | 11 | Period 11 | 2/22/2018 12:00:00 AM | 3/21/2018 12:00:00 AM |
the above is an example of how my fiscal year look like.
Thanks in advamce.
I have 3 columns "Month" & "Achievement" & "Location" that I need to create 1 calculated column to get "Average Number" from "Achievement" column using all location but in every month separated
The average should be like the table below but I can't get the number with DAX.
Kindly any advice
Month | Achievement | Location | Average |
1/1/2017 | 80 | 101 | 85 |
1/1/2017 | 90 | 102 | 85 |
2/1/2017 | 20 | 103 | 40 |
2/1/2017 | 60 | 105 | 40 |
Hi mohamed901,
You can create column with this
Ave = CALCULATE(AVERAGE(Sheet1[Achievement]),FILTER(Sheet1,Sheet1[Month]=EARLIER(Sheet1[Month])))
Hi Mohamed,
I have a long shot solution for this.
1. Create a Date_Table with the following columns:
Date | Week | Month | Quarter | Year | Month_Begin |
1/1/2017 | 1 | 1 | 1 | 2017 | Sunday, January 1, 2017 |
1/2/2017 | 1 | 1 | 1 | 2017 | Sunday, January 1, 2017 |
1/3/2017 | 1 | 1 | 1 | 2017 | Sunday, January 1, 2017 |
… | … | … | … | … | … |
2/1/2017 | 5 | 2 | 1 | 2017 | Wednesday, February 1, 2017 |
2/2/2017 | 5 | 2 | 1 | 2017 | Wednesday, February 1, 2017 |
2/3/2017 | 5 | 2 | 1 | 2017 | Wednesday, February 1, 2017 |
For the Month_Begin column, create a calculated column using the following formula and make it a date format:
Month_Begin = 'Date_Table'[Month] & "-" & 'Date_Table'[Year]
2. Establish a relationship between the date from your achievement table to the Date of the Date_Table
3. Create your measure
Average = AVERAGE('Achivement'[Achievement])
4. Create a Matrix Table
Rows: 'Date_Table'[Month_Begin], 'Achievement'[Location]
Values: 'Achievement'[Average]
4. On your Matrix table, click on the double down arrow. You will now have your average for each Month_Begin as well as the break down for each location.
Circular Dependency error occured what to do
Hi Rsanjuan,
I think the formula for month and week won’t work here, and as you concerned, the formula can’t identify which year the month belongs to, also for the week.
For the Previous sales, the formula posted might not be the correct one, see the testing results, the formula used are all copied from the posted ones:
Which I think should be the correct one is the following:
SalesYTD =
TOTALYTD (
[TotalSales],
'Date'[Date]
)
And:
SalesLastYear =
CALCULATE([TotalSales], DATEADD(DATESYTD('Date'[Date]),-1,Year))
Those two formula need a date table to work with.
For month total and Week total, see:
"
Iso MTD :=
IF (
HASONEVALUE ( Dates[ISO Year] )
&& HASONEVALUE (Dates[ISO Month Number] ),
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER (
ALL ( Dates ),
Dates[ISO Year] = VALUES ( Dates[ISO Year] )
&& Dates[ISO Month Number] = VALUES ( Dates[ISO Month Number] )
&& Dates[Date] <= MAX ( Dates[Date] )
)
),
BLANK ()
)
Iso WTD :=
IF (
HASONEVALUE ( Dates[ISO Year] )
&& HASONEVALUE (Dates[ISO Week Number] ),
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER (
ALL ( Dates ),
Dates[ISO Year] = VALUES ( Dates[ISO Year] )
&& Dates[ISO Week Number] = VALUES ( Dates[ISO Week Number] )
&& Dates[Date] <= MAX ( Dates[Date] )
)
),
BLANK ()
)
"
Check details in the the article below:
Week-Based Time Intelligence in DAX
Before using the formula posted in the article, we need to create a date table containing the following columns:
Year, month and week number in a year.
New table function to create a datetable;
Datatable = calendar(MinDate, MaxDate);
Then add the following columns:
Year = year(Datetable[Date])
Month = month (Datetable[Date])
Weeknum = WeekNum(Datetable[Date])
After that, follow the formula mentioned in the blog to generate the YTD, MTD and WTD function.
If any further help needed, please post back.
Regards
I am following your code for calculating MTD. FILTER(ALL()) is throwing an error says that the input needs minimum of 2 arguments. Help?
@Anonymous Have you faced this issue?
Just used v-micsh-msft technique for the Week to Date calculation and it worked! Thanks!
Just a precision, you don't need a date table per say. It can work as long as you have or create your date columns (year, week, date, etc.) and alter the code accordingly.
Please follow this this for complete time intelligence calculations.
Thanks & Regards,
Bhavesh
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |