Creating a calculated table based off two related tables

[moniqueg]

Hello,

I am struggling to figure out how to create a calculated table based off two other related tables.

I have a date table for which i would like to use the month end date as a group and a secondary table of data where i would like to count the rows that fall within the month. 

Ie:

table 1 (date table)	count of received in the month (table 2)	count of closed in the month (table 2)	count of assigned in the month (table 2)
30/06/2022	50	60	45
31/07/2022	65	70	50
31/08/2022	40	20	30
30/09/2022	20	55	20
31/10/2022	35	40	25

The date table is linked to table two via the 'actual date' column that is joined to each received date, closed date and assigned date etc.

I know exactly how I would/could do this in SQL but DAX is ridiculous. Something so simple is seemingly impossible to me at this point...

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

New Table = 
ADDCOLUMNS (
    DISTINCT ( 'Calendar'[End of Month] ),
    "@Received",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Received], 0 ) = 'Calendar'[End of Month] )
        ),
    "@Closed",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Closed], 0 ) = 'Calendar'[End of Month] )
        ),
    "@Assigned",
        COUNTROWS (
            FILTER ( Data, EOMONTH ( Data[Assigned], 0 ) = 'Calendar'[End of Month] )
        )
)

[amitchandak]

@moniqueg , You need a date table, Active/inactive joined and userelationship

refer this approch

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...