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

[jfgs_2019]

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

[az38]

Hi @jfgs_2019 

try a new table like

Table = 
addcolumns(
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

LinkedIn

[jfgs_2019]

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.

[az38]

@jfgs_2019 

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

LinkedIn

[jfgs_2019]

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:

 Thanks again for your patience

[az38 · Accepted solution]

sorry @jfgs_2019  my bad

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

LinkedIn