Forum Discussion

Chamari's avatar
Chamari
New Member
11 months ago
Solved

Creating a new column that counts rows per month data from a different tables

Hi all,   I have three tables, REL, RE and Call Volumes. I am trying to create ttwo new column in the call volumes that shows a count of the number of REL and RE that occurred in that month/year. ...
Need Help
  • kushanNa's avatar
    kushanNa
    11 months ago

    Hi Chamari 

     

    are you looking for an ouput similar to this ? 

     

     

    If this is what you are looking for, then you do not need to creating calculated columns, you can do it using only measures for the calculations. I had to create a measure for each requirement and also a date table. I also noticed that your dates are in European format, which could be the reason why you are having issues formatting them.

    Please find attached a sample PBIX file I created for your reference.

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
11 months ago

Chamari Normalize date formats in all tables to a common Year-Month format.
Aggregate the RE and REL tables by month.
Join these aggregates to your Call Volumes table.
Calculate per 1000 calls metrics.

 

Power Query Steps (recommended for date normalization):

In each table, add a new column for Year-Month:
Go to "Add Column" → "Custom Column" and use:

= Date.ToText([DateColumn], "yyyy-MM")

Replace [DateColumn] with the actual date field in each table.

 

Assuming your tables are named CallVolumes, RE, and REL, and all have a YearMonth column:

-- Count RE per month
Count RE =
CALCULATE(
COUNTROWS(RE),
RE[YearMonth] = CallVolumes[YearMonth]
)

-- Count REL per month
Count REL =
CALCULATE(
COUNTROWS(REL),
REL[YearMonth] = CallVolumes[YearMonth]
)

-- REs per 1000 Calls
REs per 1000 Calls =
DIVIDE([Count RE], CallVolumes[No. Calls]) * 1000

-- RELs per 1000 Calls
RELs per 1000 Calls =
DIVIDE([Count REL], CallVolumes[No. Calls]) * 1000

Chamari's avatar
Chamari
New Member
11 months ago

Thank you so much for your quick reply.

 

I am getting stuck in a couple of places.

 

For the year month date part, the Date.ToText is not coming up as an option.

I ahve tried this one

Month_Year = FORMAT([Date],"mmm yyyy").
 
But then I get stuck here:

 

Assuming your tables are named CallVolumes, RE, and REL, and all have a YearMonth column:

-- Count RE per month
Count RE =
CALCULATE(
COUNTROWS(RE),
RE[YearMonth] = CallVolumes[YearMonth]
)

 

The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.