Use Date Dimension to Supply Missing Days

[ovetteabejuela]

Hi Community!,

I'd like to know how to accomplish this using DAX,

The table on the left has some missing dates, I'd like to use my Date dimension to supply it with the missing dates resulting to the table on the right.

I'm excited about the solution, always impressed what DAX can do and the person giving out the solution! =).

SAMPLE DATA:

Date	Employee	Data
1/1/2017	Ann Filch	401
1/2/2017	Ann Filch	773
1/3/2017	Ann Filch	875
1/6/2017	Ann Filch	274
1/7/2017	Ann Filch	953
1/8/2017	Ann Filch	186
1/9/2017	Ann Filch	494
1/10/2017	Ann Filch	408
1/11/2017	Ann Filch	266
1/12/2017	Ann Filch	636
1/13/2017	Ann Filch	406
1/15/2017	Ann Filch	814
1/16/2017	Ann Filch	944
1/17/2017	Ann Filch	774
1/18/2017	Ann Filch	834
1/19/2017	Ann Filch	167
1/21/2017	Ann Filch	796
1/22/2017	Ann Filch	838
1/23/2017	Ann Filch	854
1/24/2017	Ann Filch	779

Thanks in advance!

[Habib]

Hi @ovetteabejuela, you can use  function to achieve this.

NewTable = NATURALLEFTOUTERJOIN(DateDim,EmployeeData)

I am asuming you have Date Dimension as DateDim, and the sample data iwth EmployeeData. Make sure that your column names are different in both tables as this is limitation in current verison of the formula.

Here is the output.

[ovetteabejuela]

Thanks @Habib, partly solved my problem, is there a way we can include the Employee Name as well in the resulting Table? I see that the Name is missing.

[Habib]

Employee name is part of BI the result table. Please refer to screenshot of my previous post.

[ovetteabejuela]

I was wanting to have those names appear in the 1/4, 1/5, 1/14 and 1/20 - though I think it is possible I just don't know exactly how.

[v-shex-msft · Accepted solution]

Hi @ovetteabejuela,

You can add a calculate column to deal with this, for example :

Repaced= if ([Column]=blank(),[Value Column],[Column])

Regards,

Xiaoxin Sheng

[ovetteabejuela]

@v-shex-msft, haven't actually tried but I can see that this could actually work. Thanks!