Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
rblayamt
Regular Visitor

Sum if date between dates in the header of a matrix

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) mrr.jpg

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

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
rblayamt
Regular Visitor

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

ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.