cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rsanjuan
Helper IV
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
v-micsh-msft
Microsoft
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:

15.PNG

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

 

 

 

View solution in original post

9 REPLIES 9
gbharath_reddy
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

20204/25/2019 12:00:00 AM4/22/2020 12:00:00 AM4Quarter 41/23/2020 12:00:00 AM4/22/2020 12:00:00 AM11Period 112/20/2020 12:00:00 AM3/18/2020 12:00:00 AM
20204/25/2019 12:00:00 AM4/22/2020 12:00:00 AM4Quarter 41/23/2020 12:00:00 AM4/22/2020 12:00:00 AM11Period 112/20/2020 12:00:00 AM3/18/2020 12:00:00 AM
20204/25/2019 12:00:00 AM4/22/2020 12:00:00 AM4Quarter 41/23/2020 12:00:00 AM4/22/2020 12:00:00 AM11Period 112/20/2020 12:00:00 AM3/18/2020 12:00:00 AM
20194/26/2018 12:00:00 AM4/24/2019 12:00:00 AM4Quarter 41/24/2019 12:00:00 AM4/24/2019 12:00:00 AM11Period 112/21/2019 12:00:00 AM3/20/2019 12:00:00 AM
20194/26/2018 12:00:00 AM4/24/2019 12:00:00 AM4Quarter 41/24/2019 12:00:00 AM4/24/2019 12:00:00 AM11Period 112/21/2019 12:00:00 AM3/20/2019 12:00:00 AM
20184/27/2017 12:00:00 AM4/25/2018 12:00:00 AM4Quarter 41/25/2018 12:00:00 AM4/25/2018 12:00:00 AM11Period 112/22/2018 12:00:00 AM3/21/2018 12:00:00 AM
20184/27/2017 12:00:00 AM4/25/2018 12:00:00 AM4Quarter 41/25/2018 12:00:00 AM4/25/2018 12:00:00 AM11Period 112/22/2018 12:00:00 AM3/21/2018 12:00:00 AM

 

the above is an example of how my fiscal year look like.

 

Thanks in advamce.

 

mohamed901
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.

 

Kindly any advice 

 

MonthAchievement LocationAverage 
1/1/20178010185
1/1/20179010285
2/1/20172010340
2/1/20176010540

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:

 

DateWeekMonthQuarterYearMonth_Begin
1/1/20171112017Sunday, January 1, 2017
1/2/20171112017Sunday, January 1, 2017
1/3/20171112017Sunday, January 1, 2017
2/1/20175212017Wednesday, February 1, 2017
2/2/20175212017Wednesday, February 1, 2017
2/3/20175212017Wednesday, 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. 

Sohail5558
New Member

Circular Dependency error occured what to do

 

v-micsh-msft
Microsoft
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:

15.PNG

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

 

 

 

View solution in original post

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. 

BhaveshPatel
Community Champion
Community Champion

Please follow this this for complete time intelligence calculations.

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors