Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Getting order date dates in Calendar table

Hi all,

I want to build a calendar table which will have dates from Order date column in Order table.
Order date column will have uneven dates , like it may have April 1,2,3, but may not 4, some dates will be missing 
and in some cases some months in some years will also be missing

Please help

Thanks in advance

Need Help
  • OguzMavice's avatar
    OguzMavice
    2 years ago

    Hi Anonymous , 

     

    you can create a date table based on your order table like that; 

     

    Date Table= CALENDAR(MIN('order'[date]),MAX('order'[date]))

     then if you generate relationship between Date Table[date] and order[date] by both direction, you can reach your target.

4 Replies

  • OguzMavice's avatar
    OguzMavice
    Resolver II
    2 years ago

    Hi Anonymous , 

     

    When creating standart date table by dax, we need to make some edition on it. Below code, I added two line codes apart from classic date tabe dax. Let me mark them with bold format.

    First , I created "Selection" column in order to filter uneven day number, and I used "Mod" function. If Mod(number,2)=1 , it means number is uneven. 

     

    Then, I filtered out this uneven day numbers like selection=1. 

     

    I am adding final result of data as image at below. Please follow the day number column in the image, which is your target.

    DAX DateTable =
    var table1=ADDCOLUMNS (
        CALENDAR(DATE(2024,1,1), DATE(2024,12,31)),

        "Year", YEAR([Date]),
        "Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"),
        "Quarter No", CEILING(MONTH([Date])/3, 1),
        "Month No", MONTH([Date]),
        "Month Name", FORMAT([Date], "MMMM"),
        "Month Short Name", FORMAT([Date], "MMM"),
        "Month Short Name Plus Year", FORMAT([Date], "MMM,yy"),
        "DateSort", FORMAT([Date], "yyyyMMdd"),
        "Day Name", FORMAT([Date], "dddd"),
        "Details", FORMAT([Date], "dd-MMM-yyyy"),
        "Day Number", DAY ( [Date] ),
        "Selection",MOD(DAY ( [Date] ),2)
    )
    var table2 = CALCULATETABLE(table1,FILTER(table1,[Selection]=1))
    return table2
     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi OguzMavice 

      My order date has years like in below picture

      if I select only 2008, it have only date 3/31/2008 that's it

      So I just want to show whatever dates in Order date in Calendar table

      Please reply,
      Thanks

      • OguzMavice's avatar
        OguzMavice
        Resolver II
        2 years ago

        Hi Anonymous , 

         

        you can create a date table based on your order table like that; 

         

        Date Table= CALENDAR(MIN('order'[date]),MAX('order'[date]))

         then if you generate relationship between Date Table[date] and order[date] by both direction, you can reach your target.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

    Thanks for your efforts & time in advance.

     

    Best regards,
    Community Support Team_Binbin Yu