Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a transactional table that contains all Items/ Products, that were sold in the corresponding week.
The table has a structure like this:
Date | Product | Value |
08.02.2021 | AA1 | 100 |
08.02.2021 | AA2 | 105 |
08.02.2021 | AA3 | 100 |
15.02.2021 | AA1 | 100 |
15.02.2021 | AA4 | 100 |
This table is linked with our calendar table and our Department Dimensional table.
The entries are made once every week.
Now I would like to determine 3 results:
How would you calculate this?
In the end, I would like to use a Matrix with a Department Level as a row to show these results.
Hi,
Please see below for a potential solution to your question. A measure is calculated to sum the sales in the period (week or month) before. You can then filter our records with of without sales in the previous period.
Working code available here.
Hope it helps.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hi joshua1990:
Your request is similar to the calculation of New Customer Old customer, and Return Customer. So the first thing you need to clearly define what is the New production ?
1. the week of the current filter contents = the week of the first sales week in your whole fact table
2. OR the week of the current filter contents = the week of the launch date week in your product dimension table
If the definition is 1, SQLBI.com DAX Pattern has the article to explain how to calculate.
Yes, but these would be dynamic table variables that you can create directly in DAX and then throw away again when you return the results. That way you can compare any time period to any other time period in a flexible way. I wouldn't be worried about peformance for that. Of course you'll have to test it and see if you get acceptable performance out of it.
you stuff the transactions for the two dates you want to compare into separate table variables and the use EXCEPT() to find the difference.
Let's say table A are the products sold on Feb 8 and table B the products sold on Feb 15.
EXCEPT (B,A) answers question 2, EXCEPT(A,B) answers question 3.
Thanks. Is there any other way?
There are always multiple ways to do that. What do you not like about my proposal?
@lbendlin Thank you so much for your support! I am just wondering - from a Dax Performance Point of View - if this is a good approach If I use to review the last 3 years on a weekly basis. Please correct me if I am wrong, but EXCEPT compared tables. That would mean that I have to create a table for each week, right?
User | Count |
---|---|
25 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
27 | |
12 | |
12 | |
12 | |
6 |