DATES Table - relationship not working completely

Hello, 

I think you are making a mistake while creating a calendar. why don't you follow the attached video and create the calendar and try if it works or not? 

Here is the sample code which is used in the video.

let
    Source = (Start_Date as date, End_date as date) =>
    let 
    StartDate = (Start_Date),
    EndDate = (End_date),
    DurationofDays = Duration.Days(End_date - Start_Date),
    Dates = List.Dates(Start_Date,DurationofDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Column1]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Column1]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Column1]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Year", "Day", each Date.Day([Column1]), Int64.Type),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Day", "Week of Month", each Date.WeekOfMonth([Column1]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Week of Month", "Month Name", each Date.MonthName([Column1]), type text),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Month Name", "Day Name", each Date.DayOfWeekName([Column1]), type text)
   
in
    #"Inserted Day Name"
in
    Source

Hi Anonymous,

What relationship did you create between the FullCalendar and  Historical FYTD table?

---

 THen I created 2 tables on the canvas, one based on the historical data query (order count) with the "Month-Year name" column from the query and another one with the order count from the query and with "Month-Year name" column from the Data table. 

The count # is the same however the Month-Year no# do not match for some months. The table created with the "Month-Year name" from the data table I created returns 19 orders with blank for Month-Year. 

Not sure why this happens, can you help? 

 

---

By my test based on your description, I cannot reproduce your issue. Could you share some screenshots to describe your scenario better?

If it is convenient, please share the sample table structure and your desired output so that we could help further on it.

Best  Regards,

Cherry

Hi Anonymous,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.)

Best  Regards,

Cherry