cancel
Showing results for
Did you mean:
Helper IV

## Calculating Month-To-Date and Week-to-Date

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?

1 ACCEPTED SOLUTION
Microsoft

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 =

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

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

10 REPLIES 10
New Member

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

Frequent Visitor

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.

Regular Visitor

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.

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

Hi mohamed901,

You can create column with this

Ave = CALCULATE(AVERAGE(Sheet1[Achievement]),FILTER(Sheet1,Sheet1[Month]=EARLIER(Sheet1[Month])))

Anonymous
Not applicable

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

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.

New Member

Circular Dependency error occured what to do

Microsoft

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 =

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

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

Microsoft

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?

Anonymous
Not applicable

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.

Community Champion

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.

Announcements