Compare current month IDs with previous month IDs to know the status of a transaction

[HareshChalla]

I’m facing a scenario where I’m not finding a possible solution. My data is in the following manner:

Source Data Period, Tran ID, Tran Date, Amount

Jun-23	A1	08-06-2023	200
Jun-23	A2	10-06-2023	300
Jun-23	A3	10-05-2023	400
Jul-23	A1	08-06-2023	200
Jul-23	A2	10-06-2023	300
Jul-23	A4	14-07-2023	500
Jul-23	A5	22-07-2023	600
Aug-23	A2	10-06-2023	300
Aug-23	A4	14-07-2023	500
Aug-23	A6	02-08-2023	900
Aug-23	A7	17-08-2023	1000

Now the task to be performed is to compare the data of any selected month to its previous month and bring out the transactions which are cleared or not using Tran ID value.

The canvas should have a Source Data Period Slicer (through which the end user can select any period), Card Visual showing No of Cleared items (Transactions available in previous month but not available in current month), Card Visual showing No of Open items (Transactions available in previous month and current month), and Card Visual showing No of Newly Added items (Transactions not available in previous month but are available in current month)

Suppose if the user selects the month of Aug-23, then the result should show that there are 2 transactions which are cleared in the month of July (A1 and A5), 2 transactions are pending since July through Aug (A2 and A4), and 2 transactions are newly added in the month of Aug (A6 and A7)

Another Example: if the user selects the month of July, then the result should show that there is 1 transaction cleared in the month of June (A3), 2 transactions are pending since June through July (A1 and A2), and 2 transactions are newly added in the month of July (A4 and A5)

I’ve tried creating calculated tables, different types of measures but none of the approaches are working and I’m not able to achieve the desired output. Anyone please help.

[v-stephen-msft · Accepted solution]

Hi @HareshChalla ,

I solved this by creating a separate Data Period table as a slicer.

As you can see, it's Date type.

The Period column in the main table is also a date type. If your column is of type text, set it to a date type, or try replacing it with a date column in the formulas below.

No relationship:

I created the following measures.

Cleared items = 
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS+_CURRENT=_PREVIOUS,_PREVIOUS+_CURRENT)
RETURN _IF

No of Cleared items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Cleared items]>0))

Open items = 
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS=_CURRENT,_CURRENT)
RETURN _IF

No of Open items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Open items]>0))

Newly Added items = 
VAR _SEL=SELECTEDVALUE('Table 2'[Data Period])
VAR _PRE=EOMONTH(_SEL,-1)
VAR _PREVIOUSYEAR=YEAR(_PRE)
VAR _PREVIOUSMONTH=MONTH(_PRE)
VAR _CURRENT=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&[Data Period]=_SEL))
VAR _PREVIOUS=CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER(ALLSELECTED('Table'),[Tran ID]=MAX('Table'[Tran ID])&&YEAR([Data Period])=_PREVIOUSYEAR&&MONTH([Data Period])=_PREVIOUSMONTH))
VAR _IF=IF(_PREVIOUS+_CURRENT=_CURRENT,_CURRENT)
RETURN _IF

No of Newly Added items = CALCULATE(DISTINCTCOUNT('Table'[Tran ID]),FILTER('Table',[Newly Added items]>0))

The result is as follows.

Best Regards,

Stephen Tao

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