cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

You could just do it all in a measure then:

 

Measure 11 = 
VAR __table = SELECTCOLUMNS('Table11',"Account",[Account],"Bill Month",[Bill Month])
VAR __table1 = ADDCOLUMNS(__table,"Column",IF(MONTH([Bill Month]) = 1,12,MONTH([Bill Month]) - 1))
VAR __table2 = ADDCOLUMNS(__table1,"Column 2",COUNTROWS(FILTER(__table1,[Account] = EARLIER([Account]) && MONTH([Bill Month]) = EARLIER([Column]))))
VAR __table3 = ADDCOLUMNS(__table2,"Column 3",IF(MONTH([Bill Month]) = 12,1,MONTH([Bill Month]) + 1))
VAR __table4 = ADDCOLUMNS(__table3,"Column 4",VAR __table4a = FILTER(__table3,[Account] = EARLIER([Account]) && MONTH([Bill Month]) = EARLIER([Column 3]) && [Column 2] = 1) RETURN IF(ISBLANK(COUNTROWS(__table4a)),1,BLANK()))
RETURN
SUMX(__table4,[Column 4])

Attached, same table as before.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Super User
Super User

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hey,

 

this solution basically uses a similar approach as in 1st answer, I'm sorry, i did not read your initial post exactly. But it's this pattern (I added this to the pbix from my 1st answer):

  • Create a table of events
  • add a measure
  • filter the appropriate events (here all "chains" with a minimum length of 2)
  • aggregate the events, here a simple countrows

 

1st create these two measures:

 

_check NoValue = 
IF(ISBLANK([Total quantity]),1,BLANK())

and

 

_sequence = 
    var filterIdxMonth = MAX('Calendar'[RunningMonthIndex])
    var allMonthIdx = 
            CALCULATETABLE(
                VALUES('Calendar'[RunningMonthIndex])
                ,FILTER(
                    ALL('Calendar')
                    ,'Calendar'[RunningMonthIndex] < filterIdxMonth
                )
            )
    var startmonth =
        CALCULATE(
            LASTNONBLANK('Calendar'[RunningMonthIndex],[_check NoValue])
            ,ALL('Calendar')
            ,allMonthIdx
            )
    var finalValue = 
        COUNTROWS(
            FIlTER(
                allMonthIdx
                ,'Calendar'[RunningMonthIndex] >= startmonth && 'Calendar'[RunningMonthIndex] <= filterIdxMonth
            )
        )
return
    IF(ISBLANK([Total quantity]),0,finalValue)

The final measure is 

NoOfChains = 
var MinLengthOfChain = 2
return
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE(
                'fact'
                ,'Customer'[Customer]
                ,'Calendar'[RunningMonthIndex]
            )
        ,"seq", [_sequence]
        )
        ,[_sequence] = MinLengthOfChain
    )
)

This returns the following:

image.png

The measure [_sequence] assigns an index to the consecutive events implicitly ordered by the RunningMonthIndex and restarts indexing after a NoValue has been encountered.
The measure [_sequence] is then used to filter the MinLengthOfChain = 2.

 

Hopefully this is helpful.

 

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Super User
Super User

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hi,

 

Why are you not counting row 3 and 4 as well.  2018-3-1 and 2018-4-1 are also consecutive.  So shouldn't the answer be 5?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User
Super User

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hey @Ashish_Mathur

I guess your question why the answer is not 5 depends solely on the requirements, from my experience with any kind of a sequence (as I call it) there is no single answer for a given dataset.

I added a new page to the linked pbix from my first answer and here is a little screenshot

 

image.png


@manojsv16  has been asking for the number of "chain of consecutive events," where the minimum length of a chain has to be 2.
There are just 4 chains that meet this requirement.
If one looks closely, my solution is in a way very simplified, because the measure NoOfChains flags the sequence number where the sequence index equals 2, and then these flags are counted. A more complex solution may flag all events in a sequence that meets the requirement but still counts 4 on the total row. But this will exceed the space that's available here.

Regards,
Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
manojsv16 Regular Visitor
Regular Visitor

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hi Tom,

 

Thanks a lot again for providing the solution and appreciate your effort. 

 

I tried the solution provided and getting the result in your dataset. But, I have the below observation, can you please help me to solve the issue. I am sorry for distrubing you again.

  

  • If i applied the measures in my sample dataset, _sequence measure is not reflecting the expected and value is getting reset based on account number, not on the account with consecutive billing. The total i am getting from NoofChains measure is 3, but the expected is 6. Please find the screenshot attached and correct me if i am doing wrong
  • If i use the date slicer in your data model and the select the range of the date for an account which has more than 2 consecutive billing, NoofChains measure shows no value
    • ex: Customer A has the consecutive billing in 09/2018 - 12/2018. Without any date slicer, the value shows 1 as expected. when i apply date range from 11/2018-12/2018, NoofChains measures shows no value, but expected is 1. 

Datarange_chains.PNG

Sequence.png

Super User
Super User

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hey @manojsv16 ,

 

in my current solution the measure  "_sequence" does not restart indexing considering the selected timeframe, for this reason, there is no "2"  that can be filtered and therefore also no NoOfChains value.

I don't know when I will find the time to have a closer look to this.

 

Regards,

Tom

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
manojsv16 Regular Visitor
Regular Visitor

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hi @TomMartens ,

 

Thanks for response!! 

 

Sure. Please take a look whenever you have time. Meantime, I would also try from my end. 

 

I really appreciate the your effort for solving the issues.

 

Regards,

Manoj

manojsv16 Regular Visitor
Regular Visitor

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hi @TomMartens ,

 

I am facing the similar kind of scenario. Can you please let me know how to restart the index for the measure "_sequence" as discussed in earlier conservation.

 

I would really appreaciate any suggestion

 

Thank you!!

 

 

 

 

 

 

 

 

 

 

Super User
Super User

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hey @manojsv16 ,

 

basically the restart of the index is not adding an increment to an exisiting variable under certain circumstances as one might use in different programming languages, this is due to the lack of "real" iterators like for/next or do/while (but we also should not underestimate the power of table iterators).

Instead the reindexing is based on "simple" counting. What's been counted is the number of rows, that is closest (the one the closest to the "current" Month" the "RunningMonthIndex") to the Month that is flagged by the "_check NoValue" Measure. I guess in a way it's valid to say that the "last" blank value interrupts the counting and restarts the index.

 

Hopefully this provides the necessary information you are looking for.

Regards,
Tom

 

P.S.: Maybe you might consider to select one of the posts in this thread as an answer, as this might also help others.

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

Highlighted
manojsv16 Regular Visitor
Regular Visitor

Re: Measure to calculate count of accounts which has 2 or more consecutive bill months

Hi @TomMartens ,

 

Thanks for the response. I would really appreciate if you could share the sample PBIX report with the suggestion provided in the above thread. 

 

Thank you again!!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 134 members 1,769 guests
Please welcome our newest community members: