Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to get the date of the next ID in the same Order (Pedido), to make afterwards a DateDiff and have as a result the days spended in that state (Estado). For example I need to fill up the blue column (Dia_Sig) with the "correct" date. In ID "31" I should have data as shown, plus "Dia_Sig" 20/02/2021, to then calculate the days and have a next column with "18" as a result.
I need to know how many days I spend in each "Estado", having as final result that the Order (Pedido) lasted 38 days, between 01/02/2021 (Initial Date) and the (Last Date) 11/03/2021. I have the PBIX, and Excel file to attach and send, for better understanding.
Solved! Go to Solution.
Since you want to create a calculated column then you can write it as follows:
Dia_Sig =
Var SelectedID = 'Control Emails'[ID]
RETURN
CALCULATE (
Min ( 'Control Emails'[Fecha] ),
FILTER (
ALLEXCEPT ( 'Control Emails', 'Control Emails'[Pedido#] ),
'Control Emails'[ID] > SelectedID
)
)
The measure to calculate he duration could be:
Duration =
IF (
HASONEVALUE ( 'Control Emails'[Pedido#] ),
DATEDIFF (
MIN ( 'Control Emails'[Fecha] ),
MAX ( 'Control Emails'[Dia_Sig] ),
DAY
)
)
(last column)
Please check the adjusted file.
Since you want to create a calculated column then you can write it as follows:
Dia_Sig =
Var SelectedID = 'Control Emails'[ID]
RETURN
CALCULATE (
Min ( 'Control Emails'[Fecha] ),
FILTER (
ALLEXCEPT ( 'Control Emails', 'Control Emails'[Pedido#] ),
'Control Emails'[ID] > SelectedID
)
)
The measure to calculate he duration could be:
Duration =
IF (
HASONEVALUE ( 'Control Emails'[Pedido#] ),
DATEDIFF (
MIN ( 'Control Emails'[Fecha] ),
MAX ( 'Control Emails'[Dia_Sig] ),
DAY
)
)
(last column)
Please check the adjusted file.
Thank you very much, I can now go forward, with this help. Regards!!!
I need to calculate the Fecha_Modif as it is shown in the picture...that should be Dia_Sig, not the one I have now in blue. That should be the next (Initial) Date, for the next ID of the same Order (Pedido#). I shouldn't have Blanks!! >> Id 11 should be 01/02/2021, and Id 31 20/02/2021
It doesn't work or I don't understand what you want to do...Which should be the way to attach the report, and excel file. Sorry I am new in the Forum, and don't know to work with it.
You may upload the file to your OneDrive or GoogleDrive and share the link here
You can try this calculated column:
Dia_Sig =
CALCULATE (
MAX ( [Fecha] ),
FILTER (
ALL ( MyTable ),
MyTable[Pedido] = MyTable[Pedido]
&& MyTable[Fecha] > MyTable[Fecha]
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |