Creating a calculated table based off two related tables

Question

Hello,&nbsp;I am struggling to figure out how to create a calculated table based off two other related tables.&nbsp;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.&nbsp;&nbsp;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/202250604531/07/202265705031/08/202240203030/09/202220552031/10/2022354025&nbsp;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.&nbsp;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 Answer

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.

&nbsp;

&nbsp;
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] )
        )
)
&nbsp;

Forum Discussion

Creating a calculated table based off two related tables

2 Replies

Related content

Unable to create Date Dimension and Date related measures

Filtercontext with date relation

Calculated Column using RELATED

Create Calculated Column from Related Table

Calculated Column: RELATED & USERELATIONSHIP

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