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
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
http://portal.enterprisedna.co/courses/powerbisuperuserworkshop/lectures/1772347
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
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
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
