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.
Hi, quite new to Power BI, would like to get a tip on how to build something like I have in an excel worksheet (see image)
So, the green part is a table from an MS query and then the yellow columns are basically a runoff date I use as header to bring conditional sums into the relevant month (the column header month). I have subscriptions that generate revenue btw the 'started at' date and the 'cancelled at' date (from green table) and in each of the yellow columns I want that 'calculated MRR' to be summed if the subscription is started on or before the month of the header and canceled after the month of the header. I would like those months to be a dimension in Power BI so I can still play with that MRR per month adding some other dimensions available in the table
Hope the intention is somehow clear, thanks in advance
Solved! Go to Solution.
Hi,
I recommend you create a calendar table and connect it with your green "fact table" via date. Aftewards you can place months from your calendar to a matrix as a row. Then create a measure which has the filter conditions like you described earlier. E.g. CALCULATE(SUM(FACTTABLE[Value]),ALL(Calendar),Calendar[YearMonthNumber]<=MAX(Calendar[YearMonthNumber]))
Here is an example on how to create a date table by SQLBI: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Ping me with @ if you have further questions. I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
Thanks, this put me on the right track, I think I have it now.
Hi,
I recommend you create a calendar table and connect it with your green "fact table" via date. Aftewards you can place months from your calendar to a matrix as a row. Then create a measure which has the filter conditions like you described earlier. E.g. CALCULATE(SUM(FACTTABLE[Value]),ALL(Calendar),Calendar[YearMonthNumber]<=MAX(Calendar[YearMonthNumber]))
Here is an example on how to create a date table by SQLBI: https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Ping me with @ if you have further questions. I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
Proud to be a Super User!
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |