Filter data in the report period

Question

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.&nbsp;If anyone knows how to help me.Thank you!

kgrice · Answer

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 · Answer

the measure to bring the number of hours workedbut on the table it always returns the same value for every month&nbsp;

angelobrg · Answer

Hi,&nbsp;when I drag this measure to the table, the table is all empty.What can it be?&nbsp;&nbsp;Thanks.&nbsp;

v-micsh-msft · Answer

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])&lt;='table'[date],
                           And('table'[date]&lt;=LASTDATE('table'[date]),
                               'table'[ID])
                           )
                       )
                  )&nbsp;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&nbsp;

angelobrg · Answer

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]) &lt;= 'glpi glpi_tickettasks' [date]; AND ('glpi_tickettasks glpi' [date] &lt;= LASTDATE ('glpi_tickettasks glpi' [date]); 'glpi glpi_tickettasks' [taskcategories_id]))))Thanks for your help!&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Filter data in the report period

6 Replies

Related content

Filtering Record History by Period

Filters not works while selecting multiple Period

Showing 13 periods

Filtering Table report

Dynamic Calculation Different Period