Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Can anyone help me to complete the formula?
I want to build myself the date table in a Multi Fact Table environment but I have an error. Thanks
DATE = ADDCOLUMNS(CALENDAR (DATE(YEAR (MIN
(MIN(FactInternetSales[DueDate]))),1,1),
(MIN(FactResellerSales[DueDate]))),1,1),
(MAX
(MAX(FactInternetSales[DueDate]))),12,31),
(MAX(FactResellerSales[DueDate]))),12,31),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Quarter", "Q" & INT ( FORMAT ( [Date], "q" )
)
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you.
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |