Using 2 dates (Start & End) to get one continue colomne date

[nkasdali]

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

[v-shex-msft · Accepted 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

[AlexanderBlair]

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

[nkasdali]

Thanks Alexander,
I didn't, but I realized that I can't use my filter date on this case. Exemple : if I select January, I ll have 15 days, February 31 days.

My apologies, I forgot to specify this need.

Regards