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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
HareshChalla
Regular Visitor

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

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-23A108-06-2023200
Jun-23A210-06-2023300
Jun-23A310-05-2023400
Jul-23A108-06-2023200
Jul-23A210-06-2023300
Jul-23A414-07-2023500
Jul-23A522-07-2023600
Aug-23A210-06-2023300
Aug-23A414-07-2023500
Aug-23A602-08-2023900
Aug-23A717-08-20231000

 

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.

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

Hi @HareshChalla ,

 

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

As you can see, it's Date type.

vstephenmsft_1-1692948736124.png

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.

vstephenmsft_2-1692948763433.png

 

No relationship:

vstephenmsft_0-1692948723887.png

 

 

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.

51.png

 

                                                                                                                                                         

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.           

View solution in original post

1 REPLY 1
v-stephen-msft
Community Support
Community Support

Hi @HareshChalla ,

 

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

As you can see, it's Date type.

vstephenmsft_1-1692948736124.png

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.

vstephenmsft_2-1692948763433.png

 

No relationship:

vstephenmsft_0-1692948723887.png

 

 

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.

51.png

 

                                                                                                                                                         

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.           

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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