Forum Discussion

nkasdali's avatar
nkasdali
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago
Solved

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

3 Replies

  • AlexanderBlair's avatar
    AlexanderBlair
    Icon for Advocate I rankAdvocate I
    8 years ago

    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's avatar
      nkasdali
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago
      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
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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