Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jfgs_2019
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.

Screenshoot.png

 


Can someone help me?

Thank you

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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

 


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

View solution in original post

5 REPLIES 5
az38
Community Champion
Community Champion

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


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

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:

 

S2.png

 

 

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
Community Champion
Community Champion

@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


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

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:

image.png

 Thanks again for your patience

az38
Community Champion
Community Champion

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

 


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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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