Filter data in the report period

[angelobrg]

Hello!

I'm starting at power bi and I'm certain difficulty to generate the following report:

I need the sum of a specific field values (in seconds) of a given id (id field value 1) but requires that the report a month to appear.

the table is so (required fields):

date (contains dates)
actiontime (contains the values that must be added in seconds)
id (contains numerical values from 1 to 4)

Summarizing need to filter the records that have id = 1 and add the values of actiontime field. In the detailed report that is displayed by period month / day and the sum is displayed in hours.

If anyone knows how to help me.
Thank you!

[KGrice]

Hi @angelobrg. If I understand correctly, you want to sum the actiontime field, which is recorded in seconds, and then convert that to hours. To do that, you can create a new measure that adds the seconds and divides the total by 3600 (divide seconds by 60 to get minutes, then by 60 again to get hours). Format in the Modeling tab to show however many decimal places you prefer, and give it a name that makes sense to you.

actiontime in hours = SUM(TableName[actiontime]) / 3600

You also want to calculate that specifically for id=1. You can create a second measure that builds on the first:

actiontime in hours ID1 = CALCULATE([actiontime in hours], TableName[id]=1)

You'll also need to format that measure, as the formatting is not inherited from previous measures.

This measure will also take into account the row context of any way you divide up your data, e.g., by month in a table.

[angelobrg]

the measure to bring the number of hours worked

but on the table it always returns the same value for every month

[angelobrg]

Hi,

when I drag this measure to the table, the table is all empty.What can it be?

Thanks.

[v-micsh-msft]

Hi angelobrg,

Check to see if the following formula would meet your requirements:

Under Power BI Desktop data view, click the new measure icon, then input the following formula:

AcHours := Calculate(
                Sum('table'[ActionTime])/3600, 
                Filter('table', 
                       And(FIRSTDATE('table'[date])<='table'[date],
                           And('table'[date]<=LASTDATE('table'[date]),
                               'table'[ID])
                           )
                       )
                  )

The above formula would calculate the sum of the values between the time range in the current context, and then we need to build another slicer to filter the ID.

See the result:

If you only need the data from ID = 1, replace ‘table’[ID] with ‘table’[ID]=1.

By the way, which measure that you put into the table show no values? If there is no filter, the measure should return the total value.

If any further help needed, please feel free to post back.

Regards

[angelobrg]

Thanks for your attention Michael,

I created the suggested action, it does not show me any syntax error. But when I put the measure on the table is the error attached stating that a date column with duplicate values was specified in the call to the FIRSTDATE function.

Follows the measure:

Hours by type of care = Calculate (sum ('glpi_tickettasks glpi' [actiontime]) / 3600; filter ('glpi_tickettasks glpi'; AND (firstdate ('glpi_tickettasks glpi' [date]) <= 'glpi glpi_tickettasks' [date]; AND ('glpi_tickettasks glpi' [date] <= LASTDATE ('glpi_tickettasks glpi' [date]); 'glpi glpi_tickettasks' [taskcategories_id]))))

Thanks for your help!

[v-micsh-msft]

Hi angelobrg,

Please take a try to add the distinct within FIRSTDATE and LASTDATE function.

FIRSTDATE(DISTINCT('glpi_tickettasks glpi' [date]))

LASTDATE(DISTINCT('glpi_tickettasks glpi' [date]))

See if writing in this way would solve the issue. If not, then please share more details about the data model.

Regards