Data Modelling base on Custom Time Intelligence Format

[Yatz86]

Hi all,

My data source is from SQL table where it has already been gathered and transform into one output table.

There is a report within SQL table whereby the script procedure will time stamp base on the specific week data is loaded.

e.g if data is loaded 25-Jun 2018, the custom time stamp format would be as text and shows as “2018.26” current Year/ Current Week column.

Would like to know what would be the recommended practice/approach to such data format in PowerBi if i were to make comparisons;

e.g. Difference of Week 26 Total Ordered Quantity against Week 25 Total Ordered Quantity and base on several parameter.

Attached test data as reference.

Test data.pbix (606.6 KB)

Thanks & Best Regards
Hidayat

[sam.mckay]

Recommendation here is to always try to get that column into a date column. You can do this with a few steps inside the query editor.

This is a must so that you can add in the date table to your model. You don’t want to really ever use data like this to run date calculations. You must utilize a date table everytime time. It’s essential

Then you can use either standard time intelligence functions which make this work super easy or you can use a few non standard time intelligence patterns like I’ve showcased a few times.

Here’s one example

I’m unable to look at anything in the query editor.

Are you able to add any dates instead via sql.

I could look at working out a query editor solution here, but it’s seems far more sensible to complete this at source and bring in via your sql code.

Remember proper dates are essential in Power BI and make your life about 10 times easier for analysis then the many many workarounds you require is you don’t have them.

For more detail on the importance of date table see below

Importance of date tables

[Yatz86]

Hi Sam,

Thanks for the suggestion and routing to the tutorial video!
Unfortunately, I am unable to add dates column into the current SQL table as it involves altering table on the database, a lot of additional work.

I end up creating a separate lookup table for time stamp and a relationship to the fact table.

I copied the exact dax structure in the video, but the visual does not allow me to show anything.

test.pbix (701.7 KB)

Regards
Hidayat

[sam.mckay]

Somehow you will have to get a date into that table. This is the only way to get you model working well and making it intuitive going forward.

I think this is going to have to be fixed in the query editor somehow. Where you will need to change the reference into the date. So 2018.20 will have to become 1/7/2018 - something like this.

I’ve mocked up an example that you could follow

Then we need to manipulate the date table a little bit to get the first date of every week and year.

First I reference the date table

Then only keep these two column, so delete all others

Then we remove duplicates

Now we have a table of unique ending dates

[Sorry I just realized you want to include the year in this table as well…I just don’t want to have to redo post here]

Then we want to merge these two columns

This is what is should look like now

Hopefully you’re catching my ideas here

Because then we merge this into the testing table I created which would be your columns somehow

You see now I have the right date according to that week

This is what I would do.

See how you can go with these ideas

Sam

[Yatz86]

Hi Sam,
Thanks for the direction, it works!

Found myself deep dive into other time intelligence calculation module which solved some of other related questions I have such as prevent calculations for forward dates, If statement when there is no data for previous week and comparing against budget.

Regards
Hidayat

[sam.mckay]

That’s great. Sam