Trade Data and Leave Data Analysis

[Abhay Gadiya]

Hi

I have Trade Data and Employee leaves data with following tables in the data Model.

I have attached file here - https://www.dropbox.com/s/v20we9pdqleipht/Trades Analysis.xlsx?dl=0


1. Employee Data - Employee ID and Employee Name
2. Employee Leaves - Leave Start Date, End Date, Days and Employee ID
3. Trade Data - Trade Date, CUSIP #, Employee ID

Table 2 and 3 are related to Table 1 with Employee ID.

4. Calendar - I have one year Calendar table for 2015.

Leave table is linked to calendar with Start Date and Trade data is linked to calendar table with Trade date.

My objective is here to check if there was any trade being booked from employee id when he / she was on leave.

Thank you in advance for your help.

 

[etaf]

i used a countifs
=COUNTIFS(M:M,">="&R4,M:M,"<="&S4,O:O,U4)
I put this in column W row 4 and copied down

It appears most have
Unless I'm miss understanding the sheet

 

[Ozeroth]

What you are wanting is similar to an 'events in progress' measure.

This paper has a couple of version of 'events in progress' measures (pages 21 & 27).
http://www.sqlbi.com/wp-content/uploads/DAX-Query-Plans.pdf

After playing around with your model, here are two possible "Trade Count During Leave" measures I came up with based on the examples in the paper. Do these give the expected result?

=
COUNTROWS (
    GENERATE (
        Trades,
        FILTER (
            CALCULATETABLE ( 'Leaves', ALL ( Calendar ) ),
            CONTAINS (
                DATESBETWEEN ( Calendar[Date], 'Leaves'[Start Date], 'Leaves'[End Date] ),
                Calendar[Date], Trades[Trade Date]
            )
        )
    )
)

=
COUNTROWS (
    GENERATE (
        Trades,
        FILTER (
            GENERATE (
                CALCULATETABLE ( 'Leaves', ALL ( Calendar ) ),
                DATESBETWEEN ( Calendar[Date], 'Leaves'[Start Date], 'Leaves'[End Date] )
            ),
            Calendar[Date] = Trades[Trade Date]
        )
    )
)

These rely on an employee never having two rows in the Leaves table with ranges that cover the same date (i.e. two overlapping blocks of leave for the same employee). I assume this would never happen, otherwise would need to be modified.

 

[Abhay Gadiya]

Thank you for response but I am looking Dax. I was aware about CountIfs but since I have multiple slicer using I will be performing my analysis using excel functions is very complex and slow at times. I will try Ozeroth suggestion below.

 

[Abhay Gadiya]

Thank you Ozeroth. Your suggestions seems to be working.

Thank you also for providing the link for explanation. I will would definitively read it to understand this.