How to Aggregate the sum of data from one column based on two dates fields from other table

[shalujoyce]

Hi,

I am creating a report using data from two tables. one dimension and one fact table. I have Start date and End date from dimension table and No of persons from fact table joined using a key.

Now in the output report i need the aggregate sum of the No of persons from fact table based on key (from fact & dim) between the start date and end date from the dimension table.

Please refer the screen shots below for the reference. Please help me to calculate the aggregate sum of No of persons between the start date and end date.

dimension table -input 1fact table- input 2

[v-frfei-msft · Accepted solution]

Hi @shalujoyce ,

Pbix as attached.

[parry2k]

@shalujoyce create relationshion between these two tables on key, create a table visual, drop all columns from dim table, make sure there is no aggregation used for any of the column, drop number of person fro fact table in same table visual and changes its aggregation to sum (which is going to be sum  by default), you will have your output.

[shalujoyce]

Thank you for the response.

I have tried this approach but not getting the aggregate sum. getting only the first data  of the corresponding key from fact table, even though selected sum for  the No of persons from fact table.

ie , for key 1 ,no of persons=3 instead of 13

 for key 2 , 2 instead of 8

for  key 3 , 2  instead of 17

please advise.

[parry2k]

@shalujoyce make sure data type of your no of person column is number, it is not text.

[parry2k]

@shalujoyce or share pbix file if it doesn't contain sensitive data, you can share using onedrive/google drive.