Getting order date dates in Calendar table

[Anonymous]

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

[Anonymous]

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

[OguzMavice]

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]

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 · Accepted solution]

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.