One of my favourite financial systems business scenarios is to track the financial metrics at given period with different previous periods.
Here we can see an interesting metrics scenario and simplest way of solving using DAX functions and considering AdventureWorks Tabular model for this demo.
Track Sales, Sales YTD, Prev Year Sales and Prev Year Sales YTD and need to implement in the model rather than DAX query at report to maintain the flexibility of reporting tool.

SalesYTD:=TOTALYTD(SUM('Internet Sales'[Sales Amount]),'Date'[Date])

Prev Year Sales:=CALCULATE([Internet Total Sales],SAMEPERIODLASTYEAR(  'Date'[Date]  ))

Prev Year Sales YTD:=CALCULATE([Internet Total Sales],SAMEPERIODLASTYEAR( DATESYTD( 'Date'[Date] ) ))


Points to consider:
1. Please note that Prev Year Sales and Prev Year Sales YTD are different
2. DAX calculation context used DATESYTD
3. Consider one of the similar scenarios discussed at msdn forum with possible DAX context issues and solutions using CALCULATE, SAMEPERIODLASTYEAR and DATESYTD.

Categories: DAX Tags:
  1. July 18, 2013 at 7:46 am

    I have to show my thanks to this writer for rescuing me from this predicament.
    Right after searching through the internet and getting solutions which were not productive,
    I assumed my life was done. Existing devoid of the strategies to the issues you’ve resolved all through this article is a crucial case, and those that could have in a negative way damaged my career if I hadn’t noticed your blog post.
    Your main understanding and kindness in taking care
    of the whole thing was very helpful. I’m not sure what I would’ve done if I
    hadn’t come upon such a solution like this. I can at this time look ahead to my future. Thanks very much for the expert and effective guide. I won’t think twice to suggest the sites to any person who will need
    guidelines about this situation.

  2. August 8, 2013 at 2:13 pm

    always i used to read smaller articles or reviews that
    as well clear their motive, and that is also happening with this piece of writing which I am reading at this place.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: