Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everybody,
I've been trying to find a solution for this problem for a while now, and I am not able to figure it out. I am hoping there are some Power BI Experts that can help me out with this one.
I have a table like below containing invoicing rules like this (with sample data):
Customer | Item | Price | From | Till (made with DAX) |
John | Spotify | € 10 | 02-2022 | 05-2022 |
John | Netflix | € 8 | 06-2022 | |
John | Spotify | € 12 | 05-2022 | |
Thomas | Spotify | € 10 | 02-2022 | |
Thomas | Netflix | € 8 | 05-2022 | 06-2022 |
Thomas | Netflix | € 15 | 06-2022 |
Because I find it very hard to analyse this date (especially because it is combined with other tables), I would like to convert this to a table like below for the coming two years:
Customer | Item | Price | Month |
John | Spotify | € 10 | 02-2022 |
John | Spotify | € 10 | 03-2022 |
John | Spotify | € 10 | 04-2022 |
John | Netflix | € 8 | 06-2022 |
John | Spotify | € 12 | 05-2022 |
John | Spotify | € 12 | 06-2022 |
Thomas | Spotify | € 10 | 02-2022 |
John | Spotify | € 12 | 03-2022 |
John | Spotify | € 12 | 04-2022 |
John | Spotify | € 12 | 05-2022 |
John | Spotify | € 12 | 06-2022 |
Thomas | Netflix | € 8 | 05-2022 |
Thomas | Netflix | € 15 | 06-2022 |
Based on this second table, it is way easier to combine the price for a specific month with the amount of users and some other data that affects the overall income. But I can't find a way to create table 2 based on the data in table 1.
Does anybody have any idea on how to add a table 2 to the datamodel?
Solved! Go to Solution.
Hi , @Mister_PowerBI
Based on the data you provided, it's hard for me to see what rules table2 is based on.
And the [Till] i think it is created by the other table by dax like a calculated column .
The first line is from February to February, but below there is only February to April. Is there a deviation in my understanding of your needs?
Second, if you want to generate a new table with start and end dates, you can use the GenerateSeries or calendar functions to generate a date table for each row, and then use crossjoin() to merge them.
If this method does not meet your needs, you can provide us with your special sample data and the right desired output sample data in the form of tables, so that we can better help you solve the problem.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @Mister_PowerBI
Based on the data you provided, it's hard for me to see what rules table2 is based on.
And the [Till] i think it is created by the other table by dax like a calculated column .
The first line is from February to February, but below there is only February to April. Is there a deviation in my understanding of your needs?
Second, if you want to generate a new table with start and end dates, you can use the GenerateSeries or calendar functions to generate a date table for each row, and then use crossjoin() to merge them.
If this method does not meet your needs, you can provide us with your special sample data and the right desired output sample data in the form of tables, so that we can better help you solve the problem.
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
User | Count |
---|---|
77 | |
75 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |