How to sum Hours by year and month in format YYYY/MM?

[croberts21]

I have Free PBI Desktop v2.100.1182 (64 bit free version) on Windows 10. I'm still pretty new to PBI and work on it in my limited spare time.

I have 2 fields on a single table. The table is Jobcost. The fields are STARTDATE and HOURS and EMPID. I have the filter set to show only me as the EMPID. There can be multiple STARTDATE and HOURS per day. I want to sum the hours by month and year. I'm having trouble making a measure which shows the month and year as Format() gives me an error.

I've been doing research for about 90 minutes and have not succeeded in getting this working. Here are the measures I have tried:

Date_Month = FORMAT(Jobcost[Startdate],"YYYY/MM")

The error I get for this one is: "A single value for column 'startdate' in table 'Jobcost' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, "

Date_Month = FORMAT(RELATED(Jobcost[Startdate]),"YYYY/MM")

The error for this one is: "The column 'Jobcost[startdate]' either doesn't exist or doesn't have a relationship to any table available in the current context."

The Jobcost table has 9 million records.

Any ideas how I can get this to work? Thank you!

EDIT: Made field name "Startdate" more consistent in this post.

[Anonymous · Accepted solution]

Hi @croberts21 ，

I make a simple like below.

Please create a calculated column.

aaMonthNum = FORMAT('Jobcost'[STARTDATE]+TIME(0,0,0),"mm/yyyy")

And then create a measure like this.

aaTotalHrs = SUM('Jobcost'[HOURS])

Then drag them into the visual.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[croberts21]

Thank you that works! But it wasn't sorting the dates right so i changed the date format to yyyy-mm and it sorted correctly by date. PBI doesn't treat this field as a date for some reason.

[amitchandak]

@croberts21 , id adte do no have any time stamp  then in visual put

STARTDATE

and a measure

total = sum(table[hours])

assuming hours is a number

[croberts21]

II added this measure:

aaTotalHrs = Sum(Jobcost[jcacthours])

(the Hours field is actually called "jcacthours".)

 

So I end up with the field "jcstartdate" and my measure aaTotalHrs. It still sums by day, not by month and year.

 

The output should be by month and year like:

11/2021 10

12/2021 11

1/2022 12

2/2022 13

I made up the total hours per month.

I even tried to make a Measure that shows the year and month like this:

aaMonthNum = Format(Jobcost[startdate],"YYYY/MM")

 

But I get the error:

"A single value for column 'startdate' in table 'Jobcost' cannot be determined.

This can happen when a measure formula refers to a column that contains many

values without specifying an aggregation such as min, max, "