cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors