Forum Discussion

Rsanjuan's avatar
Rsanjuan
Advocate III
9 years ago
Solved

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?

Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    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

     

     

     

10 Replies

  • v-micsh-msft's avatar
    v-micsh-msft
    Microsoft Employee
    9 years ago

    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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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. 

    • AndrewJames's avatar
      AndrewJames
      Microsoft Employee
      6 years ago

      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?

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    Please follow this this for complete time intelligence calculations.

     

     

    Thanks & Regards,

    Bhavesh

  • mohamed901's avatar
    mohamed901
    Regular Visitor
    8 years ago

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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. 

    • ekkapob's avatar
      ekkapob
      New Member
      8 years ago

      Hi mohamed901,

       

      You can create column with this

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

  • gbharath_reddy's avatar
    gbharath_reddy
    Regular Visitor
    8 years ago

    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.

     

  • DRNEWTR's avatar
    DRNEWTR
    Frequent Visitor
    4 years ago

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