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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
challaharesh
New Member

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-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 with 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 period Aug-23, then the result should show that there are 2 transactions which are cleared in the period Jul-23 (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-shex-msft
Community Support
Community Support

HI @challaharesh,

According to your description, It sound like a common date range of records list compare requirement, you can take a look the following link about similar requirement to use EXCEPT, INTERSECT functions to compare with these records lists:

Solved: Check if record exists in previous month - Microsoft Fabric Community

Since power bi does not support create dynamic calculated column/table based filter effects, you may need to create a unconcerned date table as source of filter with following measure formulas:

New Tran =
VAR currDate =
    MAX ( Table1[Tran Date] )
VAR _history =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] < currDate )
    )
VAR _current =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] = currDate )
    )
VAR trans_New =
    COUNTROWS ( EXCEPT ( _current, _history ) ) + 0
RETURN
    trans_New
    
Finished Tran =
VAR currDate =
    MAX ( Table1[Tran Date] )
VAR _history =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] < currDate )
    )
VAR _current =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] = currDate )
    )
VAR trans_Finished =
    COUNTROWS ( EXCEPT ( _history, _current ) ) + 0
RETURN
    trans_Finished
    
InProcess Tran =
VAR currDate =
    MAX ( Table1[Tran Date] )
VAR _history =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] < currDate )
    )
VAR _current =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] = currDate )
    )
VAR trans_InProcess =
    COUNTROWS ( INTERSECT ( _history, _current ) ) + 0
RETURN
    trans_InProcess

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

HI @challaharesh,

According to your description, It sound like a common date range of records list compare requirement, you can take a look the following link about similar requirement to use EXCEPT, INTERSECT functions to compare with these records lists:

Solved: Check if record exists in previous month - Microsoft Fabric Community

Since power bi does not support create dynamic calculated column/table based filter effects, you may need to create a unconcerned date table as source of filter with following measure formulas:

New Tran =
VAR currDate =
    MAX ( Table1[Tran Date] )
VAR _history =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] < currDate )
    )
VAR _current =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] = currDate )
    )
VAR trans_New =
    COUNTROWS ( EXCEPT ( _current, _history ) ) + 0
RETURN
    trans_New
    
Finished Tran =
VAR currDate =
    MAX ( Table1[Tran Date] )
VAR _history =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] < currDate )
    )
VAR _current =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] = currDate )
    )
VAR trans_Finished =
    COUNTROWS ( EXCEPT ( _history, _current ) ) + 0
RETURN
    trans_Finished
    
InProcess Tran =
VAR currDate =
    MAX ( Table1[Tran Date] )
VAR _history =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] < currDate )
    )
VAR _current =
    CALCULATETABLE (
        VALUES ( Table1[Tran ID] ),
        FILTER ( ALLSELECTED ( Table1 ), [Tran Date] = currDate )
    )
VAR trans_InProcess =
    COUNTROWS ( INTERSECT ( _history, _current ) ) + 0
RETURN
    trans_InProcess

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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