Cumulative Yearly sales vs Previous Year

[ViktorT]

Maybe this question is already asked using different wording that I am not aware of, please feel free to just link me to that if so.

I have managed to make the cumulative sales per year based on the input from the forum. However I experience difficulties in comparing YoY numbers. I constructed following measures 

Acc Sales (SEK) = 
CALCULATE(
Sum(Sales[GS value acc. (SEK)]);filter(All(Sales[YearMonth]);Sales[YearMonth] <= MAX(Sales[YearMonth]) && Year(Today())=Year(Sales[YearMonth]));Sales[YearMonth])

Acc LY Sales (SEK) = 
CALCULATE(
Sum(Sales[GS value acc. (SEK)]);filter(All(Sales[YearMonth]);Sales[YearMonth] <= MAX(Sales[YearMonth]) && Year(Today())-1=Year(Sales[YearMonth]));Sales[YearMonth])

To use these measures in the correct way, I cannot use the Date hierarcy of the field YearMonth, I have to use the continous date which means I cannot remove the year.

I changed the formula to this, using a month field instead of a date field. And this is where it starts behaving wierd in my eyes. 

Acc LY (SEK) = 
CALCULATE(
	Sum(Sales[GS value acc. (SEK)]);filter(All(Sales[YearMonth]);Sales[YearMonth] <= MAX(Sales[YearMonth]) && Year(Today())-1=Year(Sales[YearMonth]));Sales[Month])

Acc LY (SEK) = 
CALCULATE(
	Sum(Sales[GS value acc. (SEK)]);filter(All(Sales[YearMonth]);Sales[YearMonth] <= MAX(Sales[YearMonth]) && Year(Today())-1=Year(Sales[YearMonth]));Sales[Month])

If I use both year and month in cumulates in the correct way, if I remove the year only using month, current years numbers cumulates, LY shows only the total, but cumulates for where there are no data in current year.

Is my question making any sense? What am I doing wrong here?

Regards

VIktor

[Anonymous]

Hi, I have a list of 5 State Financial years ranging from 2013 to 2018. I am able to calculate difference between two consecutive years(2017,2018) and difference between current year 2018 with any previous year but not able to calculate difference for previous years like (2015,2017)

The DAX functions I used here are: YTD= Total YTD([Value], (Date), “6/30”)
PYTD = Calculate([YTD], Datesbetween(Date), Date(2013,7,1), Date(2017,6,30)))

Can someone assist me here? Thanks!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Hi, I would have loved to share but cannot due to confidentiality.

Please let me know if my DAX formulas are incorrect.

[Ashish_Mathur]

Hi,

I cannot visualise your result actually.  Share a dummy dataset which i can paste into Excel and show the expected result.

[Anonymous]

Sure, I will

[Anonymous]

Hi Avinash,

I have attached two files with dummy data, first file is 2015 vs 2018 where the Difference between these two years is coming fine.

Second file is comparison between 2015 vs 2017 where Difference is not being populated at all. Please let me know if this helps.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[AlbertoFerrari · Accepted solution]

Hi,

May I suggest you to take a look at the Time patterns at DAX Patterns?. There you will find many examples of calculations like the one you need to perform.

If that does not help, then we can work on your model (for this, I would need a sample PBIX file) but, as of now, your code seems a complex solution for a simple problem, because you are not taking advantage of the standard time intelligence functions.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[ViktorT]

Thanks Alberto

It just seems to be so strange that you need to have an "external" date table, and then everything is so simple 😃

Regards
Viktor

[AlbertoFerrari]

Indeed, it is strange. The thing is that with an external table you gain a lot of flexibility in terms of what you can compute, if they did implement it with simpler functions, then it would have been much simpler to write standard calculations, but it would have been very hard to write the advanced ones.

As strange as it is, I think it is a good balance between power and simplicity. Or, if you prefer, I would not know how to make it better, so I take it as a very good choice 🙂

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com