YTD till current week in DAX

[Anonymous]

Hello,

I want to create YTD calculations which are based on the current week, ie from Jan 2019 to the current week.
Then another measure that is from Jan 2019 to the current week -1 and so on.
Can we do that with running totals based on YTD?
Similarly, I want the YTD calculations based on the current month, last month and last to last month ie, from Jan 2019 to Dec 2019(current month) and so on.

It has to change every year, month and week. Please help me with this.

Thanks
Shubham

[PaulDBrown]

@Anonymous 

If I may, let me suggest an alternative approach.

1) Create your Date table and include columns for Week Number (function WEEKNUM), Month Number (function MONTH), Year (Function YEAR) and month name (optional) .

2) Calculate the measures for SUM of your values and Cumulative SUM:

   Sum of Values = SUM(table[values])
   Cumulative Sum (this year) = CALCULATE([sum of Values],
                                   FILTER(ALL(Calendar),

                                   Calendar[date] <= MAX(Calendar[date])
                                   && Calendar [Year] = YEAR(TODAY()))

Cumulative Sum (Last year) = CALCULATE([sum of Values],
                                   FILTER(ALL(Calendar),

                                   Calendar[date] <= MAX(Calendar[date])
                                   && Calendar [Year] = YEAR(TODAY())-1)

3) Calculate your YTD cumulative up to this week (this year)=
                                CALCULATE([Cumulative Sum (this year)],

                                FILTER(ALL(Calendar),

                                Calendar [Week Number] = WEEKNUM(TODAY())
                                ))

4) Calculate your YTD cumulative up to this week -1 (this year)=
                                CALCULATE([Cumulative Sum (this year)],

                                FILTER(ALL(Calendar),

                                Calendar [Week Number] = WEEKNUM(TODAY()) -1
                                ))
5) Calculate your YTD up to this month (This year)=

                                  CALCULATE([Cumulative Sum (this year)],

                                FILTER(ALL(Calendar),

                                Calendar [Month Number] = MONTH(TODAY())
                                ))

6) Calculate your YTD up to this month -1  (This year)=

                                  CALCULATE([Cumulative Sum (this year)],

                                FILTER(ALL(Calendar),

                                Calendar [Month Number] = MONTH(TODAY()) -1
                               ))

WEEKNUM(TODAY()) Returns the week number for the current week 
MONTH(TODAY())  Returns the month number for the current month 

YEAR(TODAY()) Returns the year number for the current year

So  if you want to go back x number of periods, just include -1 or -2 or -x behind each expression.

For values for last year, just use the [Cumulative Sum (Last year)] in the measures to create new ones

I hope you can see there's a pattern...

Anyway, just thougth an alternative might be interesting...

[Icey]

Hi @Anonymous ,

Sorry, I was stuck with my original idea. Is the method @PaulDBrown provides feasible?

Best Regards,

Icey

[Anonymous · Accepted solution]

Hello @Icey 

No, I have done it my way my creating the Variable for Startdate and Enddate and then calculating the totals between those ranges.
These variables are using dynamic dates that changes based on the current date.

Though, still thanks for your help. I appreciate your efforts.

[Icey]

Hi @Anonymous ,

Glad to hear that. Please accept your reply as a solution to close this thread.

Best Regards,

Icey

[Icey]

Hi @Anonymous ,

How do you get -3.53? and -2.27? Please find the cause of showing -2.27.

Best Regards,

Icey

[Anonymous]

Because the Last Date of Subcategory O is 24/8/2019 and when we do the last date-7 in the variable, it gets the values till 21/8/2019.
Thus not taking into account the value on 24/8 and just accumulating values till 21/8

[Icey]

Hi @Anonymous ,

I create an example. You can get details in my PBIX file.

I create a Dates Table first and create relationships between the two tables.

Dates = CALENDAR ( DATE ( 2018, 12, 1 ), DATE ( 2020, 1, 31 ) )

 

Then create measures lkie so:

YTD to the current week = 
VAR LastDayOfCurrentWeek =
    MAXX (
        FILTER (
            ALL ( Dates ),
            YEAR ( Dates[Date] ) = YEAR ( TODAY () )
                && WEEKNUM ( Dates[Date], 1 ) = WEEKNUM ( TODAY (), 1 )
        ),
        Dates[Date]
    )
RETURN
    IF (
        MAX ( Dates[Date] ) <= LastDayOfCurrentWeek,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( Dates[Date] ),
                Dates[Date] <= MAX ( Dates[Date] )
                    && YEAR ( Dates[Date] ) = YEAR ( MAX ( Dates[Date] ) )
            )
        )
    )

YTD to the current month = 
VAR LastDayOfCurrentMonth =
    MAXX (
        FILTER (
            ALL ( Dates ),
            YEAR ( Dates[Date] ) = YEAR ( TODAY () )
                && MONTH ( Dates[Date] ) = MONTH ( TODAY () )
        ),
        Dates[Date]
    )
RETURN
    IF (
        MAX ( Dates[Date] ) <= LastDayOfCurrentMonth,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( Dates[Date] ),
                Dates[Date] <= MAX ( Dates[Date] )
                    && YEAR ( Dates[Date] ) = YEAR ( MAX ( Dates[Date] ) )
            )
        )
    )

YTD to the last month = 
VAR LastDayOfLastMonth =
    MAXX (
        FILTER (
            ALL ( Dates ),
            YEAR ( Dates[Date] ) = YEAR ( TODAY () )
                && MONTH ( Dates[Date] ) = MONTH ( TODAY () )-1
        ),
        Dates[Date]
    )
RETURN
    IF (
        MAX ( Dates[Date] ) <= LastDayOfLastMonth,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( Dates[Date] ),
                Dates[Date] <= MAX ( Dates[Date] )
                    && YEAR ( Dates[Date] ) = YEAR ( MAX ( Dates[Date] ) )
            )
        )
    )

YTD to the last to last month = 
VAR LastDayOfLastToLastMonth =
    MAXX (
        FILTER (
            ALL ( Dates ),
            YEAR ( Dates[Date] ) = YEAR ( TODAY () )
                && MONTH ( Dates[Date] ) = MONTH ( TODAY () )-2
        ),
        Dates[Date]
    )
RETURN
    IF (
        MAX ( Dates[Date] ) <= LastDayOfLastToLastMonth,
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( Dates[Date] ),
                Dates[Date] <= MAX ( Dates[Date] )
                    && YEAR ( Dates[Date] ) = YEAR ( MAX ( Dates[Date] ) )
            )
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @Icey 

Thanks for your response on this. I have created last week's measure as follows based on your code and it is not working fine. I am getting blank values only. Also, I will be showing the values based on the category/sub-category and not on dates in the matrix visual. Can you check last week's code and also last to last week and week after that.
Just for the record, everything needs to be cumulative/YTD. So please verify these on that basis like if we are in Jan 2020 then how these will respond.

YTD to the last week =

VAR LastDayOflastWeek =

MAXX (

FILTER (

ALL ( dT ),

YEAR ( dT[Date] ) = YEAR ( TODAY () )

&& WEEKNUM ( dT[Date], 2 ) = WEEKNUM ( TODAY (), 2 ) - 1

),

dT[Date]

)

RETURN

IF (

MAX ( dT[Date] ) <= LastDayOflastWeek,

CALCULATE (

[Total Value],

FILTER (

ALL ( dT[Date] ),

dT[Date] <= MAX ( dT[Date] )

&& YEAR ( dT[Date] ) = YEAR ( MAX ( dT[Date] ) )

)

)

)

[Icey]

Hi @Anonymous ,

It works well in my example.

Please share me a dummy PBIX file without real data and sensitive information.

Best Regards,

Icey

[Anonymous]

Hello @Icey ,
Just remove the date field from the table visual and add a category column. This formula will be blank in that case.
I am working with category and subcategories for the calculations of CW, CW-1 and so on.

[Icey]

Hi @Anonymous ,

This is my new PBIX file. Please change the relationship direction between the two tables to "Both":

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @Icey 

Thanks for your response. Please refer to the below screen. When I collapse it to just category, it is showing everything blank and not the total of it. Can you fix that?

Thanks

[Icey]

Hi @Anonymous ,

In your scenario, it can only show one year total or all total on 'Category'. If you want to show this year's total, change the DAX expressions like so:

YTD to the current week =
VAR LastDayOfCurrentWeek =
    MAXX (
        FILTER (
            ALL ( Dates ),
            YEAR ( Dates[Date] ) = YEAR ( TODAY () )
                && WEEKNUM ( Dates[Date], 1 ) = WEEKNUM ( TODAY (), 1 )
        ),
        Dates[Date]
    )
RETURN
    IF (
        HASONEFILTER ( 'Table'[SubCategory] ),
        IF (
            MAX ( Dates[Date] ) <= LastDayOfCurrentWeek,
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    ALL ( Dates[Date] ),
                    Dates[Date] <= MAX ( Dates[Date] )
                        && YEAR ( Dates[Date] ) = YEAR ( MAX ( Dates[Date] ) )
                )
            )
        ),
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALL ( Dates[Date] ),
                Dates[Date] <= LastDayOfCurrentWeek
                    && YEAR ( Dates[Date] ) = YEAR ( TODAY () )
            )
        )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey 

Thanks for the response, but it is showing blanks.
Let me break it a bit, I have calculated the Last month and corresponding values. But still stuck with week values.
Can you know how I can change using variables the last date of a category or subcategory to be equal to the last date of the current week-1? Example
Let say my category A has subcategory A1, A2, and A3. And the last date of each last transaction is in month Jan first week. Then I am calculating the CW-1 and creating a variable Last date(Date)-7 then it is going to last year(2018) and thus I am getting blank with my formula as below.

VAR StartDate = Date(YEAR(TODAY()),1,1)

VAR EndDate = LASTDATE(Table[Week last date])-7

RETURN

CALCULATE(

[Total Value],

FILTER(ALL(Table[Week last date]),Table[Week last date]>=StartDate&&Table[Week last date]<=EndDate)

)

Do you know how to fix it?

[Icey]

Hi @Anonymous ,

Can this work?

MEASEURE =
VAR StartDate =
    DATE ( YEAR ( TODAY () ), 1, 1 )
VAR EndDate =
    LASTDATE ( Table[Week last date] ) - 7
RETURN
    IF (
        EndDate >= StartDate,
        CALCULATE (
            [Total Value],
            FILTER (
                ALL ( Table[Week last date] ),
                Table[Week last date] >= StartDate
                    && Table[Week last date] <= EndDate
            )
        ),
        CALCULATE (
            [Total Value],
            FILTER (
                ALL ( Table[Week last date] ),
                Table[Week last date]
                    >= DATE ( YEAR ( TODAY () ) - 1, 1, 1 )
                    && Table[Week last date] <= EndDate
            )
        )
    )

Best Regards,

Icey

[Anonymous]

It worked for the blank value but fails in one subcategory. For example, as mentioned below, it has to show the total as -3.53, but showing -2.27.
With the Last date-7, the formula is not taking into account the value of 4/28/2019 as it is going till 4/21/2019.
Can you fix that?

Cat SubCat Value Week Last date
Int O -3.53 1/6/2019
Int O 0.00 3/10/2019
Int O 1.26 3/24/2019
Int O -1.26 4/28/2019