topic Re: Rollover in DAX Commands and Tips

Rollover

BeginnerBI — Mon, 29 Jan 2024 15:27:45 GMT

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

Re: Rollover

Greg_Deckler — Mon, 29 Jan 2024 17:17:55 GMT

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.

Re: Rollover

BeginnerBI — Tue, 30 Jan 2024 13:30:58 GMT

Sure thing and thank you Greg_Deckler Please advise

Re: Rollover

Greg_Deckler — Tue, 30 Jan 2024 18:27:44 GMT

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

Re: Rollover

Anonymous — Fri, 02 Feb 2024 02:15:04 GMT

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

Re: Rollover

BeginnerBI — Fri, 02 Feb 2024 13:28:14 GMT

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