Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I'm losing my mind trying to resolve this problem if you can help me please :
I've got a table getting all my product with a start and end date of the promotion :
so, I want to use this table to calculate the number of days which the product was in promotion
I have in my mind to build a new table with a unique date from the start to the end date
any idea how to do this?
If you have another idea, I'll be grateful for your help.
Regards
Solved! Go to Solution.
HI @nkasdali,
You can simply use below formula to create expand table with detail date range :
Detail Table = VAR _calendar = CALENDAR ( MINX ( 'Product', [Start] ), MAXX ( 'Product', [End] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( 'Product', _calendar ), [Date] >= [Start] && [Date] <= [End] ), "Product", [Product], "Date", [Date] )
Regards,
Xiaoxin Sheng
HI @nkasdali,
You can simply use below formula to create expand table with detail date range :
Detail Table = VAR _calendar = CALENDAR ( MINX ( 'Product', [Start] ), MAXX ( 'Product', [End] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( 'Product', _calendar ), [Date] >= [Start] && [Date] <= [End] ), "Product", [Product], "Date", [Date] )
Regards,
Xiaoxin Sheng
Hi,
If I understand correctly you want to have the number of days between the start and end date? A method for doing this is using the DATEDIFF function in a Calculated Column.
Number of Days = DATEDIFF(YourTableName[StartDate].[Date],YourTableName[EndDate].[Date],DAY)
Let me know if this works!
Cheers
Alex