Calculating number of days between two dates

[steph15m]

Hi All, 

I'm new to Power BI (and new to the forum).  I'm looking to build a simple forecast in Power BI based on historical averages.

For example, I have individual records with <start date> and <end date>.  I would like to take the historical average of <end date> minus <start date> and apply this average to records is my table where end date is blank.  

I also need to do this location specific, IE Dallas division would have a different avg time frame than Houston.  So even if I have two records with a start date of 1/1/2018, the resulting "forecast date" would be based on the division's average.

Any help would be appreciated! 

[Anonymous]

Hi @steph15m

You should use DATEDIFF function to calculate the number of days between two days.

Could you show us a screenshot of some sample data and the expected output?

[steph15m]

Hi @Anonymous the screenshot below is what I am trying to accomplish in Power BI.  The forecasted end date is what I would like to calculate in Power BI.  I'm taking the start date (where the end date is blank) and adding the historical division average to get the forecasted end date.  Thank you in advance for your help! 

This is the formula in cell E16 in the screenshot:

=IF(ISBLANK(C16),VLOOKUP(A16,$A$22:$C$23,3,FALSE)+B16,"n/a")

And this is the formula is cell C22 (Houston division average):

=AVERAGEIF($A$3:$A$17,A22,$D$3:$D$17)

[PattemManohar]

@steph15m I suggest to have proper format or datatypes other wise it will be bit messy as below (If you are trying to do everything at one place)

I've converted the DaysBetween to proper number format otherwise you can't do Average (As your data contains Blank values as well). So add new "DaysBetweenNew" as below

DaysBetweenNew = IF(Test49[DaysBetween]=BLANK(),0,VALUE(Test49[DaysBetween]))

Then add your "ForeCastEndDate" as below

ForeCastEndDate = 
VAR _HoustonAvg = FORMAT(CALCULATE(AVERAGE(Test49[DaysBetweenNew]),FILTER(ALL(Test49),Test49[Place]="Houston" && Test49[DaysBetweenNew] <> 0)),0)
VAR _DallasAvg = FORMAT(CALCULATE(AVERAGE(Test49[DaysBetweenNew]),FILTER(ALL(Test49),Test49[Place]="Dallas" && Test49[DaysBetweenNew] <> 0)),0)
RETURN IF(Test49[EndDate]=BLANK(),FORMAT(SWITCH(TRUE(),Test49[Place]="Houston",Test49[StartDate]+_HoustonAvg,Test49[Place]="Dallas",Test49[StartDate]+_DallasAvg),""),"N/A")

Note - It will be really helpful if you can post the sample data in copiable format instead of screenshot or image format. It will save a lot of time for data prepartion.

[steph15m]

Thank you for your reply @PattemManohar . I don't think your solution will work for me though, in my real data I have many divisions (not just two as in the example below).  And as we add new divisions I would need to update my function which is not sustainable. 

I don't know how to insert an actual file into this thread.  If you could let me know I would be happy to do so. 

[PattemManohar]

@steph15m Yes, I too expected that !! In that case, better you create a lookup table (a calculated table) with all divisions and their average daysbetween. Then we can use this lookup table to retrieve the particular division average while adding days to the start date.

[steph15m]

@PattemManohar that sounds great...how do I create a calculated table? 

[PattemManohar]

@steph15m Here is the second method that I've mentioned above using Lookup Table.

This will be your lookup table.

Test49Lkp = SUMMARIZE(FILTER(Test49,Test49[DaysBetweenNew]<>0),Test49[Place],"Avg",FORMAT(AVERAGE(Test49[DaysBetweenNew]),0))

Then, your calculated column logic will be as below:

ForeCastEndDate1 = 
VAR _CurrVal = Test49[Place]
RETURN IF(Test49[DaysBetweenNew]=0,FORMAT(Test49[StartDate]+LOOKUPVALUE(Test49Lkp[Avg],Test49Lkp[Place],_CurrVal),""),"N/A")

Same output as previous method but this approach will be more dynamic in nature for different places/divisions.

[steph15m]

@PattemManohar thank you!  Is there anyway to remove "Dallas" and "Houston" from the calculated column function?  Can I reference the column with division name instead?  Then I won't have to go thru and copy that part of the function for every division. 

[PattemManohar]

@steph15m Oops !! Missed that... Here you go...

ForeCastEndDate2 = 
VAR _CurrVal = Test49[Place]
RETURN IF(Test49[DaysBetweenNew]=0,FORMAT(Test49[StartDate]+LOOKUPVALUE(Test49Lkp[Avg],Test49Lkp[Place],_CurrVal),""),"N/A")

Updated the same in above post as well !! 

[steph15m]

Hi @PattemManohar I'm creating these tables/measures/calculated columns in Power Pivot (not Power BI).  Could you please provide the steps to do a calculated table in Power BI?  Would be much appreciated. 

[steph15m]

Thank you!  I can see how this would work, however I am having a difficult time inserting a blank table.  (I am using Power Pivot not Power BI).  Could you please help me figure out how to insert a table?  

I've followed these instructions (unsuccessfully): Create a blank table in Power Pivot

[steph15m]

@PattemManohar would you be kind enough to email me the sample PBIX file you created to solve my problem?  I will private message you my email address.

[steph15m]

Also - this doesn't need to be done all in one column.  If there is a better way to acheive a forecasted end date by record, I am open to suggestions.  It doesn't matter to me how I arrive at the solution, just that I arrive! 🙂 @PattemManohar