Aggregate of Transactions per Team & Time matching to Team asignment ValidFrom and ValidTo

[JKross]

Good morning.

I hope someone can help. 

I have a long list of transaction data. If the transaction is closed it has a StaffID in the data. In a Staff Table is the information to which Team the StaffID belonged in which Period of time. The transaction shall be calctulated for the Team the StaffID belonged to at the Date of the transction closed. In a result I want to visualize the Number of Transactions per Team per Time Slots: Year, Month, Calendarweek.

Here is an example:

Transaction Data Table
TransactionID	StaffID	DateClosed
12345678	StaffID1	19.05.2024	// Transaction is closed => Team 1
56789101			// Transaction not closed no counting

Staff Table:
StaffID1	Team	ValidFrom	ValidTo
StaffID1	Team1	01.01.2023	20.06.2024
StaffID1	Team2	21.06.2024

To be able to connect TRansaction Table with Staff Table I have created a table, that eliminates the duplicated on the StaffIDs - thus the relationship works: Transaction Data < Staff Unique <> Staff

I have tried it already with 2 measures, but the problem is that I have a double counting. When I visualize the data the transactions are counted in each team the respective StaffID is in.

MeasureTest1 => Calculates correctly, only double counting of the transactions, where Staff Changes Teams
#Trans.perTeamMA =
 SUMX(
    VALUES('Staff'[TeamPlan]);
    CALCULATE(
        COUNTROWS('Transactions');
        'Transactions'[DateClosed]<>Blank();
        'Staff'[StaffID]=MAX('Transactions'[StaffID]);
        'Staff'[ValidFrom]<=Max('Transactions'[DateClosed]);
        'Staff'[ValidTo]=Max('Transactions'[DateClosed])
    )
 )

Measure Test2 => results only Partially in correct Results. Problem are the empty ValidTo records.
#Trans.perTeamMA_TEST =
VAR _SB = CALCULATE(MAX('Transactions'[StaffID]);USERELATIONSHIP('Transactions'[StaffID];'StaffUnique'[StaffUnique]))
VAR _TransDateCosed =MAX('Transactions'[DateClosed])
RETURN
 SUMX(
    VALUES('Staff'[TeamPlan]);
    CALCULATE(
        DISTINCTCOUNT('Transactions'[TransactionID]);
        'Staff'[StaffID]=_SB;
        'Staff'[ValidFrom]<=_TransDateCosed;
        'Staff'[ValidTo]=_TransDateCosed)
    )

Any Ideas how I can do only a singular count per Team based in the Match of the Date Closed date to the ValidFrom/To Period?

[v-sathmakuri]

Hi @JKross ,

I hope this information proves helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @JKross ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-sathmakuri]

Hi @JKross ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[JKross]

Hi @v-aatheeque ,

I tried it with the Filters and the COALESCE, but the result was pretty much the same as in My First Measure Test.

Do do the Filters, I added of course the VAR  'Transactions'[StaffID] & 'Transactions'[DateClosed], so that I could filter the Staff Table to the Transaction table. 

So far, with everything I tested, this measure gets closest and it's additive per Teams. Another problem with the Filters and COALESCE it isn't additive per Team.

MeasureTest1 => Calculates correctly, only double counting of the transactions, where Staff Changes Teams
#Trans.perTeamMA =
 SUMX(
    VALUES('Staff'[TeamPlan]);
    CALCULATE(
        COUNTROWS('Transactions');
        'Transactions'[DateClosed]<>Blank();
        'Staff'[StaffID]=MAX('Transactions'[StaffID]);
        'Staff'[ValidFrom]<=Max('Transactions'[DateClosed]);
        'Staff'[ValidTo]=Max('Transactions'[DateClosed])
    )
 )

Do you have any further ideas? 

[v-sathmakuri]

Hi @JKross ,

Below measure is not double counting the transactions. 

#TransPerTeam =

VAR _StaffID = SELECTEDVALUE('Transactions'[StaffID])

VAR _DateClosed = SELECTEDVALUE('Transactions'[DateClosed])

RETURN

CALCULATE(

    DISTINCTCOUNT('Transactions'[TransactionID]),

    FILTER(

        'Staff',

        'Staff'[StaffID1] = _StaffID &&

        'Staff'[ValidFrom] <= _DateClosed &&

        (

            'Staff'[ValidTo] >= _DateClosed ||

            ISBLANK('Staff'[ValidTo])

        )

    )

)

If still you are seeing the same issue, please share us the expected results table.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

[bhanu_gautam]

@JKross , Try using

DAX
#Trans.perTeamMA_Corrected =
VAR _TransDateClosed = MAX('Transactions'[DateClosed])
RETURN
SUMX(
VALUES('Staff'[Team]),
CALCULATE(
COUNTROWS('Transactions'),
'Transactions'[DateClosed] <> BLANK(),
'Staff'[StaffID] = MAX('Transactions'[StaffID]),
'Staff'[ValidFrom] <= _TransDateClosed,
OR(ISBLANK('Staff'[ValidTo]), 'Staff'[ValidTo] >= _TransDateClosed)
)
)

[JKross]

Hi @bhanu_gautam ;

I tried it, but the double counting got even worse. 

This expression did not work, as the Blank Values couldn't be compared to the Date: OR(ISBLANK('Staff'[ValidTo]), 'Staff'[ValidTo] >= _TransDateClosed)

I altered it to:
IF('Staff'[ValidTo])=Blank(), DATE (2999,12,31);'Staff'[ValidTo]) >= _TransDateClosed)

But now every trasaction is counted multiple times, for all StaffID's that have more than one record per StaffID.

When I visualize it by Team really funny: For the same StaffID it sums up the Total of Transactions in Team1 and calculates the double of transactions in Team2 (for a Staff ID that has 2 Records in 2 Teams)

[v-aatheeque]

Hi @JKross ,
Thanks for reaching out to Microsoft Fabric Community Forum.

#Trans.perTeamMA_Correct =

SUMX(
    VALUES('Staff'[Team]),
    CALCULATE(
        COUNTROWS('Transactions'),
        'Transactions'[DateClosed] <> BLANK(),
        FILTER(
            'Staff',
            'Staff'[StaffID] = 'Transactions'[StaffID] &&
            'Transactions'[DateClosed] >= 'Staff'[ValidFrom] &&
            (
                'Transactions'[DateClosed] <= COALESCE('Staff'[ValidTo], DATE(2999, 12, 31))
            )
        )
    )
)

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

If you continue to face issues, feel free to reach out to us for further assistance!