Checking if a record has been submitted consistently in the past 3 months

[Anonymous]

Hi Folks,

I have a table that has records coming from stores with a date field, I would like to know how to check if a store has consistenly submitted a record for each of the past 3 months.

Has anyone done something like this?  Thanks in advance

[v-yueyunzh-msft]

Hi , @Anonymous 

According to your description, you want to check the  record has been submitted consistently in the past 3 months. Right?

Here are the steps you can refer to :
(1)This is my test data: in my data, the "record1,record4" meet the need.

In my logic , i get the max date of each record , and get the past three months.

(2)We need to create a calcualted column in the Table:

Year_month = Year([Date])*100+MONTH([Date])

(3)Then we can create a measure like this :

Measure = var _max_year_month = MAX('Record'[Year_month])
var _past_3year_month= YEAR(EOMONTH( MAX('Record'[Date]) ,-3)+1)*100+MONTH(EOMONTH( MAX('Record'[Date]) ,-3)+1)
var _t =DISTINCT(SELECTCOLUMNS( FILTER('Record' , 'Record'[Year_month] <= _max_year_month && 'Record'[Year_month]>= _past_3year_month) , "year_month" , [Year_month]))
return
IF(COUNTROWS(_t)=3,1,0)

(4)Then we can meet your need, the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @v-yueyunzh-msft ,

Many thanks for your help, this does give you the total of 3 consecutive records, the data structure is basically the same however each store can have multiple entries on the same day for different products. I would like to also plot the trend by month looking at the number of 3 consecutive records  for each month. I hope you can still help on this so I'll accept you initial response because it does get you the total number.

[v-yueyunzh-msft]

Hi , @Anonymous 

Hi , @Anonymous 

According to your drscription, you want to "plot the trend by month looking at the number of 3 consecutive records  for each month". Do you mean you want to get the count of the 3 consecutive records by the Year_month dimensiuon? right?

Here are the steps you can refer to :
(1)This is my test data:

(2)We can create a calendar table like this and we do not need to create relationship between tables:

Table = CALENDAR(FIRSTDATE('Record'[Date]), LASTDATE('Record'[Date]))

(3)Then we can create a measure :

Measure 2 = var _t = SUMMARIZE( ALLSELECTED( 'Record') , [Year_month] , [Record] )
var _cur_date = MAX('Table'[Date])
var _last_3_month = EOMONTH( _cur_date , -3)+1
var _t2 = FILTER(_t , [Year_month] >= YEAR(_last_3_month)*100+MONTH(_last_3_month) && [Year_month] <= YEAR(_cur_date)*100+MONTH(_cur_date))
var _t3 = ADDCOLUMNS(_t2 , "count" ,var _record = [Record] return COUNTROWS( FILTER(_t2,[Record]=_record)))
return
COUNTROWS(DISTINCT( SELECTCOLUMNS(  FILTER(_t3,[count]=3),"record",[Record]))  )

 The result is as follows:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @v-yueyunzh-msft,

Thank you for you response, this is really close.Yes I would like to the get the count of 3 consecutive reports by the  Year_month dimension. I prefer to have this in a calculated table than using a measure. Can you help?

[v-yueyunzh-msft · Accepted solution]

Hi , @Anonymous 

If you want to get as a calcualted table , you can add a column in 'Table':

Table = ADDCOLUMNS( CALENDAR(FIRSTDATE('Record'[Date]), LASTDATE('Record'[Date])) , "Year_month" , FORMAT([Date] , "yyyy-mm"))

Then you can click "New Table" and enter this:

Result Table = SUMMARIZE('Table' , [Year_month] , "count",[Measure 2])

Then we can get the result as a calculated table:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly