Time Intelligence

[shaykoooo]

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!

[Anonymous · Accepted solution]

Hi @shaykoooo,

I think it will be help if you share more detail contents and expected result.

#1, I don't think DATEADD fucntion can used to compare with previous date, you can try to use PREVIOUSYEAR/SAMEPERIODLASTYEAR fucntion to instead.

#2, Please provide more detail informations about this, such as measure formula and result.

#3, Current forecast feature only available on line chart with regular records.

Using the Analytics pane in Power BI Desktop

Reference link:

Time Intelligence Functions in DAX

Time Intelligence in Power BI Desktop

Regards,

Xiaoxin Sheng