DATES Table - relationship not working completely

[Anonymous]

Hi guys, I am new to BI and playing with some data. I have historical data with order history for last year (order ref, datecreated (as datetime), amount - which is loaded via a query. 

As I need monthly reporting, I created a new column with the below instruction (in Data view)  :

Month-Year Name = FORMAT('Historical FYTD'[datecreated], "YYYY-MMM")

I have then created a date table with below: 

MonthTable =
var FullCalendar = ADDCOLUMNS(Calendar(date(2017,1,1), date(2018,12,31)), "YEAR", YEAR([Date]), "Month", MONTH([Date]), "Month-Year Name", FORMAT([Date],"YYYY-MMM"), "Month Name", FORMAT([Date],"mmmm"), "Unique Date", format([Date], "dd-mmm-yyyy"))
return
SUMMARIZE(FullCalendar,[Month-Year Name])

In relationships, I have linked the date table  "FullCalendar" to the Historical FYTD data based on the "Month-Year Name" column. 

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? 

What's the best way to create a month-year column from a datetime column and how can I create a date table with a month-year colum to be linked with the former ? 

Thanks!

[v-piga-msft]

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

[Anonymous]

Hi all,

I uploaded pics with what I've done, hope they are visible: 

- how I created the Month-Year Name column inside the dSales table.

- the resulting column based on the above

- the date table I created and the resulting Month-Year Name column I use to create a relationship between this date table to the dSales table

- the relationship from MonthTable (or date table) to dSales table (based on Month-Year Name column)

- visual based on  order count from dSales table and Month-Year Column from dSales - it counts for each month 

- visual based on order count from dSales table and Month-Year Column from MonthTable - doesn't see any month count. 

Thanks a lot!

[v-piga-msft]

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

[PiyushZope]

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