Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
manojsv16
Helper II
Helper II

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

Hello All,

 

I have a requriement to create a measure to calculate the count the accounts which has 2 or more consecutive bill months.

Please help me in getting the total of count of account which has the consective month bills 

 

I would appreciate any help on achieving this.  Also, please let me know if you need any further information from my end. 

 

Example: 

The total count i expected to get from the table below is 6. 

 

Account 11234  has consective bills in Dec-18 to Jan-19,  Mar-19 to Apr 19.  This account doesn't have bill in the month of Feb-19

So the count for Account 11234 is 2 (1 for Dec-18 & Jan-19, 1 for Mar-19 & Apr 19). 

 

Account 87239 has consective bills in Dec-18 to  Mar-19 , May-19 to Jun-19. This account doesn't have bill in the month of Apr-19

So the count for Account 87239 is 2 (1 for Dec-18 to  Mar-19, 1 for  May-19 to Jun-19). 

 

Account 54327 has consective bills in Jan-19 to  Feb-19 , Apr-19 to Jun-19. This account doesn't have bill in the month of Mar-19

So the count for Account 54327 is 2 (1 for Jan-19 to  Feb-19 , 1 for  Apr-19 to Jun-19)

 

 

AccountBill Month
11234Dec-18
11234Jan-19
11234Mar-19
11234Apr-19
87239Dec-18
87239Jan-19
87239Feb-19
87239Mar-19
87239May-19
87239Jun-19
54327Jan-19
54327Feb-19
54327Apr-19
54327May-19
54327Jun-19

 

Thank you!!

1 ACCEPTED SOLUTION

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.

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

20 REPLIES 20
TomMartens
Super User
Super User

Hey,

 

this pbix file contains a measure based solution 🙂

It's not based on your sample data, but I think it can be easily adjusted to your data model.

There are some requirements or let's say assumptions regarding the underlying data model:

  • There is a dedicated calendar table
  • The calendar table contains a column that counts the number of months, meaning a calendar table that starts on 2017-01-01 will count the January 2018 as the 13th month in your calendar table.

This is the data of my fact table:

CustomerDatequantity

A 2018-01-01 2
A 2018-03-01 3
A 2018-04-01 6
A 2018-09-01 2
A 2018-10-01 3
A 2018-11-01 7
A 2018-12-01 3
B 2017-12-01 4
B 2018-01-01 6
B 2018-03-01 5
B 2018-04-01 3


Both customers have "orders" in March and April 2018. This means the count of customers in April 2018 should return 2:

image.png

 

 This solution is based on this DAX statement that creates a table that contains some general information about consecutive events in a table, I use this snippet in measures that derive more, meaning other information from these events:

 

...
FILTER(
            ADDCOLUMNS(
                ADDCOLUMNS(
                    SUMMARIZE(
                        'fact'
                        ,'Customer'[Customer]
                        ,'Calendar'[RunningMonthIndex]
                    )
                    ,"quantity", [Total quantity]
                    ,"prevMonthIndex", [_prevMonthIndex]
                )
                ,"Distance prevMonth", [RunningMonthIndex] - [prevMonthIndex] 
            )
            ,[Distance prevMonth] = DistanceOfMonth
        )
...

The snippet above is used in the measure 

 

NumberOfCustomer = 
var DistanceOfMonth = 1
return
COUNTROWS(
    SUMMARIZE(
        ...
        ,[Customer]
    )
)

 

I wrap a COUNTROWS(SUMMARIZE(...)) around the table snippet. The SUMMARIZE(...,[Customer]) returns a table with a single column and COUNTROWS(...) just counts the number of rows. Please don't get confused by the notation of 

...
,[Customer]
...

Here I do not reference a measure (no table name is specified), but a column that is contained inside the table virtual table used by the DAX function, namely SUMMARIZE(...). I'm not aware if this can be considered a best practice, but at least it helps me to compose my DAX statements :-), and as it does not interfere with the lineage or performance, I don't care.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom,

 

Thanks for the response!! I appreciate your effort on trying to solve the issue.

 

I tried the solution provided and it is working fine.  The solution covered most of the requirement, but there is small observations mentioned below.  

 

  • I wanted the count of bills for 2 or more consecutive months for a account.  The total count expected from the data model provided by you  is 4.  But, the total count i am getting from the measure (NumberofCustomer) is 2 which is the count of customers. 
    •  Customer A has 2 or more consecutive quantity in the month (2018-03-01 - 2018-04-01 & 2018-09-01 - 2018-12-01)
    •  Customer B has 2 or more consecutive quantity in the month (2017-12-01 - 2018-01-01 & 2018-03-01 - 2018-04-01)

 Please help me in acheving the below result. I would appreciate any suggestions. 

 

Thank you in advance!!

ConsectiveBilling.PNG

 

Regards,

Manoj

 

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/

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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!!

 

 

 

 

 

 

 

 

 

 

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.

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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!!

Hey @manojsv16 ,

 

following this link you will be able to download the pbix file I referenced throughout the conversation in this thread.

 

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Zubair_Muhammad
Community Champion
Community Champion

@manojsv16 

 

Would you like to do this in M /Power Query or you need a DAX measure only?

 

 


Regards
Zubair

Please try my custom visuals

Hi Zubair,

 

Thanks a lot for the response!!

 

I prefer DAX Measures. But, i am fine with M/ Power Query if have a better approach to solve this issue.

 

 

Thanks!!

 

HI @manojsv16 

 

Here is a Power Query Solution

Please see attached file for steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ0MjZR0lFySU3WNbRQitVBCHkl5ukaWqII+SYWoQs5FsCFLMyNjC1RzYIJIZkFE3JLTUIXQjIeIVSJLuRVCjfL1MTYyBzVeJgQkvEwISSnwoSQjIebBTU+FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Account = _t, #"Bill Month" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Bill Month", type date}}),
     AllMonths={List.Min(List.Transform(BeginningMonth[Bill Month],each Date.Year(_)))..
            List.Max(List.Transform(BeginningMonth[Bill Month],each Date.Year(_)))},
     MyTable= Table.AddColumn(Table.FromColumns({AllMonths},{"Year"}),"Months",each {1..12}),
    ExpandedMonths = Table.ExpandListColumn(MyTable, "Months"),
    #"Added Custom" = Table.AddColumn(ExpandedMonths, "Custom", each #date([Year],[Months],1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Year", "Months"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "Account", each List.Distinct(Source[Account])),
    #"Expanded Account" = Table.ExpandListColumn(#"Added Custom1", "Account") ,     
    BeginningMonth = Table.AddColumn(#"Changed Type", "Custom", each Date.StartOfMonth([Bill Month])),
    #"Removed Columns" = Table.RemoveColumns(BeginningMonth,{"Bill Month"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Account",{"Account", "Custom"},#"Removed Columns",{"Account", "Custom"},"Removed Columns",JoinKind.LeftOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"Account"}, {"Account.1"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Removed Columns",{{"Account", Order.Ascending}, {"Custom", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Account.1"}, {{"Consecutive Dates", each List.Transform(_[Custom], each Date.ToText(_,"MM-yyyy"))}, {"ConsecutiveCount", each Table.RowCount(_), type number}},GroupKind.Local),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Account.1] <> null)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [ConsecutiveCount] >= 2),
    #"Extracted Values" = Table.TransformColumns(#"Filtered Rows1", {"Consecutive Dates", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

7Mar19.png


Regards
Zubair

Please try my custom visuals

Hi Zubair,

 

Thanks a lot for the response!!

 

I tried the metioned solution and it works fine. However, there are some limitation in implementing the solution since direct query was used to get the data and limited options to do transformation. 

 

I would appreciate if you could help me in getting a measure to solve the issue.

 

Thanks!!

Hi@manojsv16

Tagging some DAX experts to help you.

 

@MFelix @TomMartens @Greg_Deckler 



Regards
Zubair

Please try my custom visuals

I'm sure that there are better ways but I did this in 4 calculated columns:

 

Column = 
IF(MONTH([Bill Month]) = 1,12,MONTH([Bill Month]) - 1)

Column 2 = 
COUNTROWS(FILTER(ALL('Table11'),[Account] = EARLIER([Account]) && MONTH([Bill Month]) = EARLIER([Column])))

Column 3 = 
IF(MONTH([Bill Month]) = 12,1,MONTH([Bill Month]) + 1)

Column 4 = 
VAR __table = FILTER('Table11',[Account] = EARLIER([Account]) && MONTH([Bill Month]) = EARLIER([Column 3]) && [Column 2] = 1)
RETURN
IF(ISBLANK(COUNTROWS(__table)),1,BLANK())

The SUM of Column 4 equals 6. Table 11 of attached.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

 

Thanks a lot for the response!!

 

I tried the solution provied, it is very useful and working fine. I am using direct query and trying to avoid calculated columns as well. 

I can create the column and column 3 in database. Direct Query doesn't allow to include countrows DAX expression. 

 

However, I would like to check whether there is way to calcuate the Column 3 and Column 4 using DAX Measure instead of calculated columns  

 

I would appreciate for any suggestion on acheiving the result. 

 

Thank you!!

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.