Forum Discussion
Post PatronDate table in multi fact environment
HI gtamir ,
Asuming that you want the max and minimum year of the two refer columns to get beginning and ending of calendar try the following:
DATE = ADDCOLUMNS ( CALENDAR ( DATE ( YEAR ( MIN ( MIN ( FactInternetSales[DueDate] ), MIN ( FactResellerSales[DueDate] ) ) ), 1, 1 ), DATE ( YEAR ( MAX ( MAX ( FactInternetSales[DueDate] ), MAX ( FactResellerSales[DueDate] ) ) ), 12, 31 ) ), "Year", YEAR ( [Date] ), "Month Number", MONTH ( [Date] ), "Month Name", FORMAT ( [Date], "mmmm" ), "Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ) )Regards,
MFelix
Super UserHi gtamir ,
Just picking up the first date on your calendar formula:
DATE(YEAR (MIN
(MIN(FactInternetSales[DueDate]))),1,1),
(MIN(FactResellerSales[DueDate]))),1,1)
In this you are picking up the DATE of the Year of the minimum of two dates and then adding them together, however you formula does not says that.
You have a DATE formula that has the following parameters (YEAR, MONTH, DAY) then you add YEAR that the paremeter is a DATE.
When you place the MIN(FactInternetSales[DueDate]))),1,1) you are adding the January first to a date column so the output is incorrect because the DATE part for the YEAR formula already comes from the MIN(FactInternetSales[DueDate]).
What I do in my formula is getting the DATE with MONTH and DAY being hard coded ( 1,1 or 12,31) and then picking up the YEAR of the MIN of the two dates.
Regards,
MFelix
gtamirThank you.