Time Intelligence Function

[Shay A]

Hello,
In my model I have a calendar table- DimDate[Date] and a fact sales table, updated until 8/2/18 and being updated on a daily basis. I need to create these three measures.

1. compare current month sales to the last year's parallel month, but, only for number of days that passed by. (ie to show sales for 1/2/18 throgh 8/2/2018 against 1/2/17 throgh 8/2/2017.
Could DATESADD do the trick?

2. Same as clause number 1 only that for 2017, I would like to show sales for ALL of the days in February 2007.
I tried SAMEPERIODLASTYEAR AND PARALLELPERIOD but it returned all of the dates in february 2017.


3. Create a dynamic forecast for sales for the current month, meaning that based on averaged daily sales since 1/2/18 until 8/2/18, I can multiply the this daily average by the number of working days in February 2018 and then I will get the linear projected sales for the current month. Next month, same thing for March, as this should be dyanmic.
I need to create a running total and divide by the number of the days that passed since the beginning of current month but don't know how to do that...

THANK YOU ALL!

 

[NARAYANK991]

Hi ,

You might answers faster if you upload your workbook with the data in it.

See if this links helps with your problem #1 :

https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/

Narayan

 

[Chihiro]

Personally, I prefer to have date dimension table to have full calendar year for years with data. Even if I only have data up to Feb 09, 2018, I usually set up dimension table to have dates from 1/1/2017 to 12/31/2018 for an example.

It's easy to deal with date comparison if needed using date range filter, rather than in built time intelligence function if needed to deal with partial month etc.

 

[Shay A]

I see, but what if I want to show current month sales, even though not finished yet, and next to it I want to show all of Februars last year sales?
Can Dateadd help?