Year to Date calculations

[sc]

Can anyone help with how to display same period last year calculation?  ie, I want to show sales this year up to today, and the comparative sales for the same period last year.  I am not very good with DAX, so need something simple.  To view comparative periods seems like a basic requirement, but I cannot see anything simple to do it.  I have a sales table, and have created a separate date table.  Any help greatly appreciated!

[TomMartens · Accepted solution]

Hey,

creating a separate date column is a great start. The most simple calculation for the Year-To-Date pattern can be accomplished using the build-in function TOTALYTD().

This statement creates a Year-To-Date calculation

Year-To-Date Value = 
    TOTALYTD(SUM('Simple Facts'[Value]),'Calendar'[Date])

And this statement does the same for the previous year:

Year-To-Date Value LY = 
TOTALYTD(
    SUM('Simple  Facts'[Value])
    ,DATEADD('Calendar'[Date]
        ,-12
        ,MONTH
    )
)

The workings of these both statements are depicted here:

If these statements do not fit your needs, than it's most probable that the following site will answer your questions:

https://www.daxpatterns.com/time-patterns/

Hopefully this gets you started

Regards

Tom

[sc]

Thanks very much Tom, that gives me a good starting point.  Is there a way to automatically recognise today's date and show last years sales up to that date only?

Regards, Sc

[TomMartens]

Glad,

that works for you, at least gives you start.

The function TODAY() may provide a solution to your latest question.

Here is an example, that may provide what you are looking for. Please be aware, that the formula below considers TODAY and the YEAR(TODAY()), but the statement may be exceuted in a context where you may not exptect it.

Year-To-Date Today = 
var dateToday = TODAY()
return
CALCULATE(SUM('Simple Facts'[Value])
    ,FILTER(ALL('Calendar'[Date])
        ,'Calendar'[Date] <= dateToday
        && YEAR('Calendar'[Date]) = YEAR(dateToday)
    )
)

If you think your question is answered, please mark a post as answer, this may help others.

Regards

Tom

[dani_always]

Can you explain what happens for the below?

"the statement may be exceuted in a context where you may not exptect it."

[sc]

Could you post the example formula with TODAY function that you mentioned in your post, it was missing from your post.

Thanks.

[TomMartens]

silly me, see my post above