Rollover

[BeginnerBI]

Hello colleagues,

We want to see how many accounts opened in December but excluding the ones that applied in December.

I have this DAX that brings me the opened accounts for December but I need to exclude the ones that applied in December (from another column - application date)

like if you filter columnopen for dec 2023 and you filter the columnapp excluding dec 2023.

the formula if we do it manually in excel would be 328(opencolumn) - decembervalues (322) = 6 that opened in december but they applied before december. Thank you

Please advise and thank you so much

[BeginnerBI · Accepted solution]

Certainly, I successfully addressed this issue by creating a new column and implementing an IF statement. Thank you for your attention to this matter.

[Anonymous]

Hi @BeginnerBI ,

Did the Greg's suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Greg_Deckler]

@BeginnerBI Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[BeginnerBI]

Sure thing and thank you @Greg_Deckler  Please advise 

[Greg_Deckler]

@BeginnerBI OK, so can you explain the logic here? You have 5 rows with an openaccount in December of 2024 so I am assuming that is the "Opened" metric. What I don't understand is the rollover metric. Are those the 2 rows that have an applications date that is not in December of last year?

If so, perhaps something like:

Rollover measure = 
  VAR __Count = COUNTROWS('Table')
  VAR __Month = MONTH(MAX('Dates'[Date]))
  VAR __Exclude = COUNTROWS(FILTER('Table', MONTH([applications]) = __Month))
  VAR __Result = __Count - __Exclude
RETURN
  __Result