cancel
Showing results for
Did you mean:
New Member

## calculate a measure based on two date columns in the same table

Hello,

I have a table with IT issues with two dates for each record : creation date and resolution date. I need to get a measure that calculates how many are created in a day and another with how many are closed in a day.

I give you an example screenshot of data and how to get the results.

Can someone help me?

Thank you

1 ACCEPTED SOLUTION
Community Champion

try more complex solution

first, create acalendar table

``Tabla prueba = DISTINCT('Todas las incidencias Soporte I'[Fecha creación])``

then in this Tabla prueba create 2 measures:

``create = calculate(countrows('Todas las incidencias Soporte I');filter(ALL('Todas las incidencias Soporte I');'Todas las incidencias Soporte I'[Fecha creación]=selectedvalue('Tabla prueba'[Fecha creación])))``
``closed = calculate(countrows('Todas las incidencias Soporte I');filter(ALL('Todas las incidencias Soporte I');'Todas las incidencias Soporte I'[Fecha resolución]=selectedvalue('Tabla prueba'[Fecha creación])))``

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
5 REPLIES 5
Community Champion

try a new table like

``````Table =
UNION(DISTINCT('Table1'[Create date]);DISTINCT('Table1'[Resolution date]));
"create";calculate(countrows('Table1');filter(ALL('Table1');'Table1'[Create date]=selectedvalue([Date]);
"closed";calculate(countrows('Table1');filter(ALL('Table1');'Table1'[Resolution date]=selectedvalue([Date]);
)``````

but I didnt check 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
New Member

Hello @az38

Thanks for the answer. But, something I have to do wrong, since it always gets the same result (attached screenshot)

My new table is:

Tabla prueba = ADDCOLUMNS(DISTINCT('Todas las incidencias Soporte I'[Fecha creación])

;"create";calculate(countrows('Todas las incidencias Soporte I');filter(ALL('Todas las incidencias Soporte I');'Todas las incidencias Soporte I'[Fecha creación]=selectedvalue('Todas las incidencias Soporte I'[Fecha creación])))
;"closed";calculate(countrows('Todas las incidencias Soporte I');filter(ALL('Todas las incidencias Soporte I');'Todas las incidencias Soporte I'[Fecha resolución]=selectedvalue('Todas las incidencias Soporte I'[Fecha creación]))))

And the result is:

One question...What is the "Date" field of the "selectedvalue" function in the solution you gave? Sorry if the question is a bit silly, but I'm a Power BI newbie.

Thanks again.

Community Champion

date inside SELECTEDVALUE is a date in each row

so, i think, its the reason why your statement doesnt work.

you should try selectedvalue([Fecha creación]) instead of selectedvalue('Todas las incidencias Soporte I'[Fecha creación])

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution
New Member

Hello again,

Thank you for the clarification regarding the "Date" column.

I've changed "'Todas las incidencias Soporte I'[Fecha creación]" to "[Fecha creación]" and I'm sorry to tell you that the solution you say still doesn't work. The error is:

the 'Fecha creación' column is missing or may not be used in this expression.

The statment is:

Community Champion

try more complex solution

first, create acalendar table

``Tabla prueba = DISTINCT('Todas las incidencias Soporte I'[Fecha creación])``

then in this Tabla prueba create 2 measures:

``create = calculate(countrows('Todas las incidencias Soporte I');filter(ALL('Todas las incidencias Soporte I');'Todas las incidencias Soporte I'[Fecha creación]=selectedvalue('Tabla prueba'[Fecha creación])))``
``closed = calculate(countrows('Todas las incidencias Soporte I');filter(ALL('Todas las incidencias Soporte I');'Todas las incidencias Soporte I'[Fecha resolución]=selectedvalue('Tabla prueba'[Fecha creación])))``

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors