Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |