Change over time - Relations & Best Practice

[Marcus_swe]

Hi, 

So i am struggeling to figure out how to handle different change of time cases. I am new to DAX and Power Bi but have played around with it for a couple of weeks.

I dont know if i am setting everything up fundamental wrong or if it just some small error along the way. 

This is the first time i am trying to build a report that shows change of time and uses a dedicated calendar table. 

So, hit me up with your best links/tips for best practice regarding change over time! 

I want do several things, for example:

Change of amout of parts over time

Change of cost over time

Compare MoM, YoY etc etc

But i cant get the relation between the date calender to work.

The data i have contains of 2 excel files and a calculated calander table. 

excel file 1: Live data, updated daily

excel file 2: History data, is a compiled over time version of the Live data sheet. So basicly a datadump of the "Live Data", exactly the same columns but data with a date stamp when the data was updated.

Format of the data is like this:

Updated from Database	Part no	Target Cost	Actual Cost	More info coumns --->
2019-04-04	720
2019-03-04	720
2019-02-04	720
2019-01-04	720
2018-12-04	720

Calculated Calender based on this:

Date = 
 ADDCOLUMNS (
 CALENDAR (DATE(2019;01;01); DATE(2020;01;01));
"Today";today();
"Yesterday";today()-1;
"DateAsInteger"; FORMAT ( [Date]; "YYYYMMDD" );
 "Year"; YEAR ( [Date] );
 "Monthnumber"; FORMAT ( [Date]; "MM" );
 "YearMonthnumber"; FORMAT ( [Date]; "YYYY/MM" );
 "YearMonthShort"; FORMAT ( [Date]; "YYYY/mmm" );
 "MonthNameShort"; FORMAT ( [Date]; "mmm" );
 "MonthNameLong"; FORMAT ( [Date]; "mmmm" );
 "WeekNo"; Format ([Date]; "ww");
  "DayOfWeekNumber"; WEEKDAY ( [Date] );
 "DayOfWeek"; FORMAT ( [Date]; "dddd" );
 "DayOfWeekShort"; FORMAT ( [Date]; "ddd" );
 "Quarter"; "Q" & FORMAT ( [Date]; "Q" );
 "YearQuarter"; FORMAT ( [Date]; "YYYY" ) & "/Q" & FORMAT ( [Date]; "Q" );
 "Heute -1 ";if([date]>=NOW()-2;"Y"; "N");
 "Heute -2";if([date]>=NOW()-3;"Y"; "N");
"Date-1";[Date]-1   )

Issue:

Let start easy and say i want to create a tabel showing total number of parts per week.

I create a "one to many" relation between date calender and history data. 

When i proceed to create a table i get this result:

I dont know why i cant get it to work. I have some thoughts that my history data is formatted in an incorrect way.

So, have I misunderstood the basics or is there something else that is fishy here?

[tex628]

Hello! 

Starting off it appears that there is something incorrect with the relationship. If you could post a picture of your datecolumn in the History table we might be able to figure out the issue! 🙂 

/ Johannes

[v-lili6-msft · Accepted solution]

hi, @Marcus_swe 

Please check if the format type Updated from Database is datetime when it is imported. if so, please add a date column by DATE Function

Column = DATE(YEAR(Table1[Updated from Database]),MONTH(Table1[Updated from Database]),DAY(Table1[Updated from Database]))

If not your case, there is something wrong in other steps, please share your sample pbix file for us to have a test.

By the way, for your other requirement, try time-intelligence-functions in dax or have a try on quick measures in power bi desktop.

https://docs.microsoft.com/en-us/dax/time-intelligence-functions-dax

https://docs.microsoft.com/en-us/power-bi/desktop-quick-measures

Best Regards,

Lin

[Marcus_swe]

Hi, @v-lili6-msft  Thank you for the reply. 

It was the format of my date column that was incorrect. 

I did not know that it was such a differance between Date or Date/Time. 

I now got it to work as i tought. 

Off to the next challenges 

**Big thanks for the links aswell! 

//Marcus