Year Over Calculation/Same Period Last Year Coming Wrong?

[Anmolgan]

Hi, I am looking to calculate Same Period Last Year calculations, I have dataset for atleast 3 years, now I want the sales to work year wise, month wise and Date range wise (All should give a comparisons with current year and last year).

For example below are the points that I am looking to work upon:

1. Calculate current sales vs last year sales (Should Be working with the year filter).

2. Calculate current sales vs last year sales (Should be Working with the month filter).

3. Calculate current sales vs last year sales (This should be working with the date range filter, for example if I want to compare current date vs last year current date, or let say current period (Start date 2 Aug 2018 and end Date 10 Nov 2018 comparison to last year)

Now I have been working on this for quite some time now, and I have tried various things:

Creating my own date table, the building Y2D, Sameperiodlast year calculations, used DateADD DAX and various other dax but I cannot validate the output for the above cases with my source database, below are the DAX calculations that I am using:

Last Year Sales = CALCULATE([This Year Sales],DATEADD('Invoice Date'[Invoice date].[Date], -1,YEAR))

This Year Sales = CALCULATE([Total Sales Sumx],DATEADD('Invoice Date'[Invoice date].[Date], 0, year))

Total Sales Sumx = SUM(Main,(Main[Sales]))

My cases 1,2 are working fine but whenever I use Date Slicer it ends up showing me the same result for a current date or a current period, how can I avoid this problem.

Any help will be deeply appreciated.

[amitchandak · Accepted solution]

As discussed

Last Year Sales = CALCULATE([Total Sales Sumx],DATEADD('Date'[Date], -1,YEAR))
 
This Year Sales = CALCULATE([Total Sales Sumx],DATEADD('Date'[Date], 0, year))

[Anmolgan]

@amitchandak thanks for your end to end support on the issue. Much appreciated!!

[amitchandak]

Your formula is right. Do few changes. Like do not use .date. If it nor having a time stamp. Also, create a date table Mark it as date table use that in your formula

Last Year Sales = CALCULATE([This Year Sales],DATEADD('Date'[Date], -1,YEAR))
 
This Year Sales = CALCULATE([Total Sales Sumx],DATEADD('Date'[Date], 0, year))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

You can refer this. Bit difference in import mode, but options are the same  :https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

Appreciate your Kudos.

[Anmolgan]

@amitchandak @ImkeF am using the data table itself and not any fact tables, can you tell me where am I going wrong because the dataset is not validating.

[amitchandak]

Can you share sample data and sample output. Mark me @

[Anmolgan]

@amitchandak I am sending the file into your private chat, its quite big but please bear with me, I will also share the sample output too for a particular date so that it will help you in validating.

[amitchandak]

@Anmolgan , I will check in my evening.

[amitchandak]

@Anmolgan 

I tried to make the Invoice date as date dimension. And it was not. Then I create a new date Invoice DT in fact and Date dimension. and joined them. I will send the file to PM. I changed few formulae, check it they are fine with you

 if you need more help make me @

Appreciate your Kudos.

[Anmolgan]

@amitchandak Hi amit appreciate your response, will surely check the files and will get back on this with you. Thanks again for all your help.

[Anmolgan]

@amitchandak I have looked at the file that you had shared, problem is on a specific date range the value of current year and last year is coming blank, how can we build the DAX so that it should work with the date range itself.?

[amitchandak]

@Anmolgan ,Can you send me that condition. Let me have a look

[Anmolgan]

@amitchandak I didnt understood the refrence of what you are talking about, as previously mentioned there are 3 things that I need to accomplish using the current year and last year function:

1. Year slicer working for current year last year (This is working earlier).

2. Month slicer working for current month last year sales. (This is working earlier).

3. Data slicer so that I can view current year last year sales for any year or date (This I need to accomplish).

I have given you a particular date sales for this and last year current date, the pbix you had sent it shows same sales for current start date and the same date + 1 year in the data slicer, need to accomplish this.

Do let me know if I am able to define the above clearly, or I had to choose some other ways of defining the same.

[ImkeF]

Hi @Anmolgan 

make sure to use the Time Intelligence Functions (DATEADD) on a column from your date table (and not the fact-tables ("Main")):

Last Year Sales = CALCULATE([This Year Sales],DATEADD('YourDateTable'[Date], -1,YEAR))

This Year Sales = CALCULATE([Total Sales Sumx],DATEADD('YourDateTable'[Date], 0, year))

Total Sales Sumx = SUM(Main[Sales])