Re: Is there a recursive way to get sequential cou...

bsheffer · ‎08-27-2021

for each account I'm counting the number of consecutive months that they have a status of seasonal but they can't have any other status in that month. My code is very long. Is there a way to write this recursively?

_consecutive_months_seasonal (m) =

if(calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 1), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal") = 0

|| calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 1), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal") = 1

, 0,

if(calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 0), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal") = 0

|| calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 0), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal") = 1

, 1,

if(calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = -1), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal") = 0

|| calculate(DISTINCTCOUNT('Fact MID_Status_History'[ACTIVITY_MONTH]), filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = -1), 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal") = 1

, 2,

3)))

there are actually 38 of these sets but when I posted the entire measure the message was marked as spam.

bsheffer · ‎08-27-2021

So I'm trying instead to find the last month the merchant was not seasonal and then do the datediff between that month and current month to find the total number of full seasonal months.

_test =

var _max_month = max('Fact MID_Status_History'[ACTIVITY_MONTH])

var _mid = min('Fact MID_Status_History'[MERCHANT_NUMBER])

var _table = calculatetable (

summarize('Fact MID_Status_History', 'Fact MID_Status_History'[MERCHANT_NUMBER], 'Fact MID_Status_History'[ACTIVITY_MONTH]

, "seasonal count", calculate(COUNTROWS('Fact MID_Status_History'), filter('Fact MID_Status_History', 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] = "Seasonal"))

, "non-seasonal count", calculate(COUNTROWS('Fact MID_Status_History'), filter('Fact MID_Status_History', 'Fact MID_Status_History'[CHANGE_DETAILS_FROM] <> "Seasonal"))),

'Fact MID_Status_History'[MERCHANT_NUMBER] = _mid

)

return

CALCULATE(

maxx(_table, [ACTIVITY_MONTH])

, filter(_table, [non-seasonal count] > 0))

for merchant 941000132272 this should be april 2021 but it is returning august 2021

merchant	activity_month	seasonal count	non-seasonal count
941000132272	8/1/2021 12:00:00 AM	31
941000132272	7/1/2021 12:00:00 AM	31
941000132272	6/1/2021 12:00:00 AM	30
941000132272	5/1/2021 12:00:00 AM	31
941000132272	4/1/2021 12:00:00 AM	4	27
941000132272	3/1/2021 12:00:00 AM		31

the counts are the number of days in the month it was in that status

please tell me how to fix my code

v-yiruan-msft · ‎08-30-2021

Hi @bsheffer ,

Could you please provide more example data from the table Fact MID_Status_History and table Dim Calendar ActivityMonth (exclude sensitive data)and your expected result? Also, please describe the detailed calculation logic and explain when it need to calculate consective count? Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

bsheffer · ‎08-30-2021

Merchant	month	realtive month number	Status
1	6/1/2021	-1	Open
1	6/1/2021	-1	Seasonal
1	7/1/2021	0	Seasonal
1	8/1/2021	1	Seasonal

I expect this merchant to have a consecutive seasonal month count of 2 because we only count months where they are only seasonal.

dim calendar activity month has date, activity_month, and relative_month_flag so

8/1/2021 8/1/2021, 1

8/2/2021 8/1/2021, 1

...

7/3/2021 7/1/2021, 0

....

6/15/2021 6/1/2021, -1

the measure tests for seasonal not found or a non-seasonal status found in a specific month. if either is true then it returns a value. Otherwise it is consecutive seasonal and drops to the next if statement where it is tested again for the prior month. This continues until there are no more months to test or a non-full seasonal month is found.

this count is calculated for each merchant.

v-yiruan-msft · ‎09-01-2021

Hi @bsheffer ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can create two measures as below to get the count of consecutive seasonal months:

Measure = 
VAR _selmerc =
    SELECTEDVALUE ( 'Fact MID_Status_History'[Merchant] )
VAR _selmonth =
    SELECTEDVALUE ( 'Fact MID_Status_History'[month] )
VAR _countofstatus =
    CALCULATE (
        DISTINCTCOUNT ( 'Fact MID_Status_History'[Status] ),
        FILTER (
            ALLSELECTED ( 'Fact MID_Status_History' ),
            'Fact MID_Status_History'[Merchant] = _selmerc
                && 'Fact MID_Status_History'[month] = _selmonth
        )
    )
VAR _countofseasonal =
    CALCULATE (
        DISTINCTCOUNT ( 'Fact MID_Status_History'[Status] ),
        FILTER (
            ALLSELECTED ( 'Fact MID_Status_History' ),
            'Fact MID_Status_History'[Merchant] = _selmerc
                && 'Fact MID_Status_History'[month] = _selmonth
                && 'Fact MID_Status_History'[Status] = "Seasonal"
        )
    )
RETURN
    IF (
        _countofstatus = 1
            && _countofseasonal = 1,
        CALCULATE ( DISTINCTCOUNT ( 'Fact MID_Status_History'[month] ) ),
        BLANK ()
    )

Count of consecutive seasonal months =
SUMX (
    GROUPBY (
        'Fact MID_Status_History',
        'Fact MID_Status_History'[Merchant],
        'Fact MID_Status_History'[month]
    ),
    [Measure]
)

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

bsheffer · ‎09-01-2021

thank you for this very interesting piece of code. I appreciate the use of allselected.

My issue is that if there is another seasonal month record (merchant = 1, activity month = 5/1/2021, relative month -2, Seasonal) then it counts that earlier month even though there was a break in full months on 6/1/2021. It should still count only 2 even if there is earlier activity.

thanks again.

v-yiruan-msft · ‎09-02-2021

Hi @bsheffer ,

Please check the cases and solutions in the following two cases, and don't confirm if it is what you want.

Column counting multiple consecutive months for every ID

count number of consecutive days

Besides that, you can provide some sample data plus backend logic and specific examples to illustrate the results you expect. We will provide a solution based on the information you provide later... Thank you.

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ziyabikram96 · ‎08-27-2021

Consective count =
var rowPickup = CALCULATE(MAX('T1'[Date]),
FILTER('Working File',
'Working File'[status] = EARLIER('Working File'[status])
&& 'T1'[Date] <= EARLIER('T1'[Date])
&& 'T1'[Data] <> EARLIER('T1'[Data])))
return
IF(rowPickup = BLANK(),
COUNTROWS(FILTER('T1',
'T1'[status] = EARLIER('T1'[status])
&& 'T1'[Date] <= EARLIER('T1'[Date]))) + 1 ,
'T1'[Date] - rowPickup)
Change T1 with your respective table name
and status with [CHANGE_DETAILS_FROM]

bsheffer · ‎08-27-2021

what is 'working file' ? How is it different from 'T1'? What is 'T1'[DATA]?

ziyabikram96 · ‎08-30-2021

Just change with your required tables and columns this will help you

bsheffer · ‎08-30-2021

seems to produce errors.

VahidDM · ‎08-27-2021

Hi @bsheffer

I think you can create variables and use them in calculation code rather than the filter , something like this:

Var _Filter =

filter(all('Dim Calendar ActivityMonth'), [_ACTUALS RELATIVE MONTH FLAG] = 1)

https://docs.microsoft.com/en-us/dax/best-practices/dax-variables

https://docs.microsoft.com/en-us/dax/var-dax

beside, you can use SWITCH code.

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

Appreciate your Kudos!!

bsheffer · ‎08-27-2021

Not sure how this would help me. I'm testing for failure and when it succeeds (meaning that the month is good) I move to the next month. Replacing pieces of code with up to 32 filter variables might make the code shorter but I don't see how this improves the logic.

parry2k · ‎08-27-2021

@bsheffer can you share sample data and expected output.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

bsheffer · ‎08-27-2021

in this case the merchant has a status of seasonal in august and july. he has a status of seasonal in jun but also had a status of open for part of the month. Therefore we only want to count the full sequential months where the status is seasonal. In this example the result would be 2.

parry2k · ‎08-27-2021

@bsheffer no idea what you are referring to, I requested sample raw data with the expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

bsheffer · ‎08-27-2021

Merchant	month	realtive month number	Status
1	6/1/2021	-1	Open
1	6/1/2021	-1	Seasonal
1	7/1/2021	0	Seasonal
1	8/1/2021	1	Seasonal

I expect the consecutive count of full seasonal months to be 2 for this merchant.

Is there a recursive way to get sequential counts? attempt 2

Besides that, you can provide some sample data plus backend logic and specific examples to illustrate the results you expect. We will provide a solution based on the information you provide later... Thank you.

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly