Help with monthly change across two columns

[tcpafc]

Hi there - I am very new to PBI and am struggling with the concept of date tables and relationships. I know there must be a very simple, elegant way to achieve what I want but I just can't make the connection (literally and figuratively).

I have a table of data, containing account numbers, and the date those accounts were opened and closed (and not all are closed), example below. 

Basically, I need to calculate the cumulative change from month to month (i.e. starting number plus number of accounts opened minus number of accounts closed), but because I can't work out how to link two different date columns to a single calendar month, I can't work out how to get the model to recognise that e.g. an account with "Date Opened" in March 2020 and an account with "Date Closed" in March 2020 should both be recognised in "Calendar Date" of March 2020. 

If any of this makes any sense, please could you help me work out what I assume is a very simple solution? The more forums I look at and YouTube vids I watch, the more confused I get! Many thanks, 

TC

Account number	Date opened	Date closed
A123	1/01/2019	3/05/2022
A234	3/04/2020
A345	20/06/2020	6/03/2025
A456	6/09/2020
A567	23/11/2020	19/01/2022
A678	9/02/2021	4/05/2023
A789	28/04/2021
A890	15/07/2021
B123	1/10/2021	1/01/2024
B234	18/12/2021
B345	6/03/2022
B456	23/05/2022	2/09/2025
B567	9/08/2022
B678	26/10/2022
B789	12/01/2023
B890	31/03/2023	12/08/2024
C123	17/06/2023
C234	3/09/2023
C345	20/11/2023	2/08/2025
C456	6/02/2024
C567	24/04/2024
C678	11/07/2024	3/01/2025
C789	27/09/2024
C890	14/12/2024

[v-achippa]

Hi @tcpafc,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[tcpafc · Accepted solution]

Thanks all - as per my message below, I ended up finding a solution, but am very grateful to everyone that provided suggestions. Every one is helping me learn, so very much appreciated 🙂 

TC

[v-achippa]

Hi @tcpafc,

Thank you for the response and confirming that the issue is resolved now. We are closing this thread for now, if you have any other issues please raise a new thread in the community forum, we are ready to assist you on that.

Thanks and regards,

Anjan Kumar Chippa

[Shubham_rai955]

To calculate monthly change using two date columns (opened and closed) in Power BI, use a single date table and set one active and one inactive relationship, then use DAX measures with USERELATIONSHIP to calculate for each date field.​

Step-by-Step Solution

• Create a single date table in your model and mark it as the date table.​

• Create one active relationship between your date table and the Date Opened column.

• Create another (inactive) relationship between the same date table and the Date Closed column.

• For monthly opens, use a simple COUNTROWS/CALCULATE measure linked to the active relationship.

• For monthly closes, use a DAX measure with USERELATIONSHIP to activate the Date Closed relationship, e.g.:

  Accounts Closed = CALCULATE( COUNTROWS(table), USERELATIONSHIP(table[Date Closed], DateTable[Date]) )

• Calculate cumulative monthly change with:

  Monthly Change = [Accounts Opened] - [Accounts Closed]

  and to get cumulative by month:

  Cumulative Change = CALCULATE( SUMX(DateTable, [Monthly Change]), FILTER(DateTable, DateTable[Date] <= MAX(DateTable[Date])) ) )[web:34][web:31]

This lets your visuals show accounts opened/closed each month and the cumulative total, based on both date columns in one calendar contex

[v-achippa]

Hi @tcpafc,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Poojara_D12, @Ashish_Mathur, @ryan_mayu and @amitchandak for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Poojara_D12]

Hi @tcpafc 

This is a very common challenge for Power BI beginners, and you’re right — it comes down to how relationships and date tables work. Power BI only allows one active relationship between a fact table and a date table at a time, which is why linking both “Date Opened” and “Date Closed” directly to a single calendar doesn’t behave as expected. In your case, the simplest and most flexible approach is to use one master Date table (a continuous list of dates covering the full range of your data) and then create two relationships between it and your account table — one to Date Opened (active) and another to Date Closed (inactive). With that setup, you can build two separate measures: one for Accounts Opened using the active relationship, and another for Accounts Closed using the USERELATIONSHIP function to activate the “Date Closed” link only within that calculation.

[tcpafc]

Thanks you both so much for your suggestions  - I ended up working it out a different way (or rather, I thought I did exactly the same as I previously had but it suddenly worked!) but I really appreciate the efforts. Not sure if I should mark as a solution or not, as I didn't use them? (I'm new here!) Thanks again, 

TC

[Ashish_Mathur]

Hi,

Based on the data that you have shared, show the expected result.

[ryan_mayu]

@tcpafc 

you can create a dim date table and create a measure

measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date opened]<=max('date'[Date])))- CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Date closed]<=max('date'[Date])&&not(ISBLANK('Table'[Date closed]))))

pls see the attachment below

[amitchandak]

@tcpafc , Please check if the two attached files can help . You need one active and one inactive join Closed will inactive for given code 

Open = CALCULATE(COUNT(Cases[CASE_NUMBER]),USERELATIONSHIP(Cases[DATE_CASE_RECEIVED],'Date'[Date]) )

Closes = CALCULATE(COUNT(Cases[CASE_NUMBER]),USERELATIONSHIP(Cases[DATE_CASE_CLOSED],'Date'[Date]),not(ISBLANK(Cases[DATE_CASE_CLOSED])))

Current = CALCULATE(COUNTx(FILTER(Cases,Cases[DATE_CASE_RECEIVED]<=max('Date'[Date]) && (ISBLANK(Cases[DATE_CASE_CLOSED]) || Cases[DATE_CASE_CLOSED]>max('Date'[Date]))),(Cases[CASE_NUMBER])))

Or Active = 
CALCULATE([Open],filter(all('Date'),'Date'[date] <=max('Date'[date]))) - CALCULATE([Closed],filter(all('Date'),'Date'[date] <=max('Date'[date])))

Active last month 

Active till last month = 
CALCULATE([Open],filter(all('Date'),'Date'[date] <=maxX('Date', dateadd('Date'[date],-1, month) )))
- CALCULATE([Closed],filter(all('Date'),'Date'[date] <=maxX('Date', dateadd('Date'[date],-1, month) )))