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 timeChange of cost over timeCompare 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 dailyexcel 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 DatabasePart noTarget CostActual CostMore info coumns--->2019-04-04720 2019-03-04720 2019-02-04720 2019-01-04720 2018-12-04720 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?

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

Frequent Visitor

7 years ago

Solved

Change over time - Relations & Best Practice

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?

Need Help

Tips and Tricks

Tutorial Requests

v-lili6-msft
7 years ago
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

3 Replies

v-lili6-msft
Community Support
7 years ago
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
  Frequent Visitor
  7 years ago
  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 :smileyhappy:
  
  **Big thanks for the links aswell!
  
  //Marcus
tex628
Community Champion
7 years ago
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

Forum Discussion

Change over time - Relations & Best Practice

3 Replies

Related content

Git Integration Best Practices

credential management best practices?

Best Practice Analyzer

Filtercontext with date relation

Copilot - theory versus practice