Forum Discussion
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 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
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]))
2 Replies
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]))
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,
