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

[Rsanjuan]

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?

[DRNEWTR]

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

[gbharath_reddy]

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.

[mohamed901]

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

[ekkapob]

Hi mohamed901,

You can create column with this

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

[Anonymous]

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. 

[Sohail5558]

Circular Dependency error occured what to do

[v-micsh-msft · Accepted 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

[AndrewJames]

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]

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]

Please follow this this for complete time intelligence calculations.

Thanks & Regards,

Bhavesh