Forum Discussion

robcrockford's avatar
robcrockford
Advocate I
10 years ago
Solved

Merging Date Fields

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...
  • Vvelarde's avatar
    Vvelarde
    10 years ago

    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]))

     

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
10 years ago

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,