Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to Identify Recurring Journal

Hi All

 

As part of GL analysis, i need to identify which journal entries are recurring in my data of journal transactions.

 

The source data is in this form -

 

DescriptionDateAmount
Journal101-01-2020962
Journal129-02-2020509
Journal131-03-2020819
Journal130-04-2020152
Journal131-05-2020508
Journal130-06-2020773
Journal131-07-2020712
Journal131-08-2020799
Journal130-09-2020202
Journal131-10-2020391
Journal201-01-2020270
Journal229-02-2020270
Journal231-03-2020270
Journal230-04-2020270
Journal231-05-2020270
Journal230-06-2020270
Journal231-07-2020270
Journal231-08-2020270
Journal230-09-2020270
Journal231-10-2020270
Journal301-01-2020183
Journal301-01-2020754
Journal301-01-2020221
Journal301-01-2020934
Journal301-01-2020119
Journal301-01-2020168
Journal301-01-2020983
Journal301-01-2020454
Journal301-01-2020193
Journal301-01-2020309

 

I need to identify which journal is recurring in all periods basis the description. For example, all the 3 journals in above dataset are recurring in all months. However, only Journal 2 is recurring in all months with same amount which i need to identify through a measure.

 

Please suggest.

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    @Aashu2093 , Create a column as


    Month-year - format([Date], "YYYYMM")

    then they have a measure like

    countx(filter(summarize(Table, Table[Description], "_1", distinctcount(Table[Month-year]), "_2", calculate(distinctcount(Table[Month-year]), all(Table))),[_1]-[_2]),[Description])

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    @Aashu2093 , Create a column as


    Month-year - format([Date], "YYYYMM")

    then they have a measure like

    countx(filter(summarize(Table, Table[Description], "_1", distinctcount(Table[Month-year]), "_2", calculate(distinctcount(Table[Month-year]), all(Table))),[_1]-[_2]),[Description])

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    Anonymous  Amitchandak's solution will only count how many distinct values, but not check if they are the same. You could expand on that method though and get there eventually I think.

     

    Another option is to turn to power query, though I'm not sure how to make it dynamic and efficient. I find using a Left Anti join for Merge in Power Query to be a really good way to reconcile journals and two different tables. You could do this with your data by filtering for this month, creating another query and filter for last month, then merge. I'm not sure if that's what you want though? Amitchandak's DAX will be more dynamic if you can expand on it.