topic Re: calculate a measure based on two date columns in the same table in DAX Commands and Tips

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

jfgs_2019 — Wed, 18 Dec 2019 15:12:51 GMT

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

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

az38 — Wed, 18 Dec 2019 17:56:15 GMT

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

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

jfgs_2019 — Thu, 19 Dec 2019 14:35:27 GMT

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.

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

az38 — Thu, 19 Dec 2019 16:10:18 GMT

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

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

jfgs_2019 — Thu, 19 Dec 2019 16:54:13 GMT

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

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

az38 — Thu, 19 Dec 2019 17:28:33 GMT

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