Forum Discussion

Rsanjuan's avatar
Rsanjuan
Icon for Advocate III rankAdvocate III
10 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...
Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    10 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

     

     

     

v-micsh-msft's avatar
v-micsh-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 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
    Icon for Microsoft Employee rankMicrosoft 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?