Sum if date between dates in the header of a matrix

[rblayamt]

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

[rblayamt]

Thanks, this put me on the right track, I think I have it now.

[ValtteriN · Accepted 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/