Merging Date Fields

[robcrockford]

Hi,

I have a fact table that contains multiple date fields:

Created Date

Quoted Date

Instructed Date

Completed Date

I wish to have counts for each of these under one month heading in a matrix table - does anyone know how this is possible?

At the moment I have added a date table which links a date to the created date, but any counts for the other fields will be based on the created date not the others.

Thanks in advance

[v-sihou-msft]

@robcrockford

In this senario, since your date table has date column link to create, it definitely will count other fields within the month based on created date. For your requirement, you should have another calendar date column in your fact table which links the date table so that all 4 kinds of date will be counted within a calendar month.

Regards,

[Vvelarde · Accepted solution]

hi @robcrockford

First create relatiosnhips between date columns and Date Table.

This should like this:

Next create measure to count for every date column:

Count-Created = CALCULATE(Count(Tabla1[Value]);Tabla1[Created Date])

Count-Quoted = CALCULATE(Count(Tabla1[Value]);USERELATIONSHIP(Calendario[Date];Tabla1[Quoted Date]))

Count-Instructed = CALCULATE(Count(Tabla1[Value]);USERELATIONSHIP(Calendario[Date];Tabla1[Instructed Date]))

Count-Completed = CALCULATE(Count(Tabla1[Value]);USERELATIONSHIP(Calendario[Date];Tabla1[Completed Date]))