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

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