Create a new table with an overview route and calculation number of days

[Whisper]

Dear power bi users,

I want to do this for the entire green table as follows,
the orange and blue background are an example. The result should be a new table like the red box.

An admission is the column "opnameid_anonym" which can consist of various changes -> column "Afdeling or Omschr".
Between each mutation I want to calculate the number of days -> column "MUT_Date".

Who can help me to create this in Dax or Power Query. (I can also provide you the dataset).
Thank you!

[Anonymous]

Hi @Whisper ,

Can you tell me how these values are calculated? DAX can do a lot of calculations, but I need to know the steps before I can convert them to code. Based on your description, you said to count the number of days, Does one row of records represent one day or is there a column dedicated to days that sums the values in the days column? In the former case you can use COUNTROWS for the calculation, in the latter case you can choose SUM.

Can you provide me with sample data? Don't need all the data, just some for testing.

Best Regards,
Dino Tao

[Whisper]

Hi Dino,

Thank you for helping me with this case 👍
The "Mut_datum" column contains the start date. I make a difference of 2 dates as in the example.

A sample of data can you find here -> https://www.frankmartens.be/data.zip 

Thank you very much!
Frank

[Anonymous]

Hi @Whisper ,

I'm sorry, but I can't open the link you provided to the sample data, and I can only test it using this screenshot you provided.
I will you in the screenshot of the four data calculated using the DAX function, but according to your requirements if you want to use a DAX will be the entire table of data are calculated is not possible, you can only one by one calculation.

Days_mut 1_A = 
VAR Event1_start =
CALCULATE(
    MIN('Table1'[MUT_DATUM]),
    FILTER('Table1', 'Table1'[OMSCHR]="Libra 3")
)
VAR Event2_start =
CALCULATE(
    MIN('Table1'[MUT_DATUM]),
    FILTER('Table1', 'Table1'[OMSCHR]="De Zeilen 2")
)
RETURN
DATEDIFF(Event1_start, Event2_start,DAY)

Days_mut 2_A = 
VAR Event2_start =
CALCULATE(
    MIN('Table1'[MUT_DATUM]),
    FILTER('Table1', 'Table1'[OMSCHR]="De Zeilen 2")
)
VAR Event2_end =
CALCULATE(
    MAX('Table1'[MUT_DATUM]),
    FILTER('Table1', 'Table1'[OMSCHR]="De Zeilen 2")
)
RETURN
DATEDIFF(Event2_start, Event2_end,DAY)

Days_mut 1_B = 
VAR Event1_start =
CALCULATE(
    MIN('Table2'[MUT_DATUM]),
    FILTER('Table2', 'Table2'[OMSCHR]="Libra 1")
)
VAR Event2_start =
CALCULATE(
    MIN('Table2'[MUT_DATUM]),
    FILTER('Table2', 'Table2'[OMSCHR]="Vega")
)
RETURN
DATEDIFF(Event1_start, Event2_start,DAY)

Days_mut 2_B = 
VAR Event2_start =
CALCULATE(
    MIN('Table2'[MUT_DATUM]),
    FILTER('Table2', 'Table2'[OMSCHR]="Vega")
)
VAR Event1_end =
CALCULATE(
    MAX('Table2'[MUT_DATUM]),
    FILTER('Table2', 'Table2'[OMSCHR]="Libra 1")
)
RETURN
DATEDIFF(Event2_start, Event1_end,DAY)

The final results are as follows:

You can put these metrics into visual objects to create the visual effects you need.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Whisper]

Hi Dino,

Strange you can't open the dataset. Have you looked in your Downloads? 

I understand your Dax calculation, thank you for it. Maybe I didn't describe it clearly.
There is 1 Fact table ("Fact_Mutaties") like the table below.
I want a new table where for each "opnameid_anonym"  when (OMSCHR) changes the number of days are calculated like in the example where I did the first 5 "Opnameid_anonym".

When there is no end date then leave it blank. 

Many thanks in advance!
New table

Fact table ("Fact_Mutaties")

MUT_DATUM	Mutatie_Uur	opnameid_anonym	AFDELING	OMSCHR
23/02/2023	20:50:00	0004AE5A-7D2C-48CD-92C9-081CCB276B1A	7	Libra 3
29/03/2023	10:50:00	0004AE5A-7D2C-48CD-92C9-081CCB276B1A	7	Libra 3
4/04/2023	17:00:00	0004AE5A-7D2C-48CD-92C9-081CCB276B1A	7	Libra 3
6/04/2023	9:34:00	0004AE5A-7D2C-48CD-92C9-081CCB276B1A	20	De Zeilen 2
27/04/2023	17:59:00	0004AE5A-7D2C-48CD-92C9-081CCB276B1A	20	De Zeilen 2
30/04/2023	9:58:00	0004AE5A-7D2C-48CD-92C9-081CCB276B1A	20	De Zeilen 2
30/04/2023	9:59:00	0004AE5A-7D2C-48CD-92C9-081CCB276B1A	20	De Zeilen 2
23/05/2023	23:00:00	0004AE5A-7D2C-48CD-92C9-081CCB276B1A	20	De Zeilen 2
17/04/2023	9:55:00	0008106E-BDF2-4831-8D59-6CA1FC4D62F4	5	Libra 1
17/04/2023	10:30:00	0008106E-BDF2-4831-8D59-6CA1FC4D62F4	5	Libra 1
24/04/2023	8:40:00	0008106E-BDF2-4831-8D59-6CA1FC4D62F4	5	Libra 1
29/06/2023	8:30:00	00132D5E-0151-469C-A79D-4BF7A4203C96	151	Nova 2
29/09/2023	8:30:00	00132D5E-0151-469C-A79D-4BF7A4203C96	151	Nova 2
2/03/2023	10:45:00	0023D137-4C61-4FC4-9896-E6D43BACE19A	13	Sirius 2
7/08/2022	19:10:00	00432BCD-BE6E-4E05-9B54-4B8D05C2B362	7	Libra 3
9/08/2022	16:00:00	00432BCD-BE6E-4E05-9B54-4B8D05C2B362	7	Libra 3
11/08/2022	9:59:00	00432BCD-BE6E-4E05-9B54-4B8D05C2B362	9	Orion 2
24/11/2022	10:00:00	0046A647-4184-4374-8583-38F35721CB63	14	Sirius 3
26/11/2022	11:00:00	0046A647-4184-4374-8583-38F35721CB63	14	Sirius 3
31/01/2023	10:05:00	00507898-44A0-48AD-8529-F32C0902B668	5	Libra 1
31/01/2023	10:23:00	00507898-44A0-48AD-8529-F32C0902B668	5	Libra 1
31/01/2023	12:15:00	00507898-44A0-48AD-8529-F32C0902B668	5	Libra 1
14/02/2023	11:25:00	00507898-44A0-48AD-8529-F32C0902B668	5	Libra 1
9/03/2022	8:30:00	0054BA9A-8AC2-4FEA-B093-113350BD125C	151	Nova 2
10/06/2022	8:30:00	0054BA9A-8AC2-4FEA-B093-113350BD125C	151	Nova 2
14/11/2022	8:30:00	0054BA9A-8AC2-4FEA-B093-113350BD125C	160	Vega
18/11/2022	7:00:00	0054BA9A-8AC2-4FEA-B093-113350BD125C	160	Vega
21/11/2022	8:30:00	0054BA9A-8AC2-4FEA-B093-113350BD125C	151	Nova 2
2/12/2022	11:30:00	0054BA9A-8AC2-4FEA-B093-113350BD125C	160	Vega
8/12/2022	10:38:00	0054BA9A-8AC2-4FEA-B093-113350BD125C	160	Vega
12/12/2022	8:30:00	0054BA9A-8AC2-4FEA-B093-113350BD125C	151	Nova 2
16/11/2022	9:55:00	0055D075-1927-4989-A58E-56DEF81808E2	14	Sirius 3
15/12/2022	8:30:00	0055D075-1927-4989-A58E-56DEF81808E2	14	Sirius 3
10/02/2023	18:15:00	005A5FF0-BD6B-4C44-92E5-C906AA74A3C7	9	Orion 2
11/02/2023	13:00:00	005A5FF0-BD6B-4C44-92E5-C906AA74A3C7	9	Orion 2
3/04/2023	15:00:00	006165D7-BB8F-4073-9E05-20B3E3F991A6	5	Libra 1
12/04/2023	17:00:00	006165D7-BB8F-4073-9E05-20B3E3F991A6	5	Libra 1
14/04/2023	12:25:00	006165D7-BB8F-4073-9E05-20B3E3F991A6	5	Libra 1
1/09/2022	9:55:00	006A81B4-EB9D-4595-B27B-E1DFC2071C1D	5	Libra 1
5/09/2022	8:00:00	006A81B4-EB9D-4595-B27B-E1DFC2071C1D	5	Libra 1
3/10/2022	15:50:00	006A81B4-EB9D-4595-B27B-E1DFC2071C1D	6	Libra 2
16/08/2022	10:00:00	0071E31E-3D2A-4D0B-946C-4C0C2233FFA1	16	Spika 1
7/07/2023	14:20:00	00838DA3-F96D-488D-9FC1-1BBF153569B4	160	Vega
24/02/2023	15:55:00	0089E521-F0F0-46F6-BA69-874A106DCE62	19	De Zeilen 1
1/03/2022	23:30:00	0090659F-A3CC-42AA-B818-657F6CB65C7C	160	Vega
3/02/2022	12:25:00	00913D1D-4EF3-4002-8D54-2CC0CC2A8CF5	5	Libra 1
1/03/2022	10:30:00	00913D1D-4EF3-4002-8D54-2CC0CC2A8CF5	5	Libra 1
5/06/2023	10:05:00	009343A4-1279-4483-A4EE-4942088306A7	7	Libra 3
6/06/2023	8:28:00	009343A4-1279-4483-A4EE-4942088306A7	7	Libra 3
26/06/2023	10:34:00	009343A4-1279-4483-A4EE-4942088306A7	7	Libra 3
27/06/2023	10:50:00	009343A4-1279-4483-A4EE-4942088306A7	6	Libra 2
4/02/2022	9:35:00	0094518F-A96F-4286-8DC0-9993E65D6A81	5	Libra 1
5/02/2022	10:00:00	0094518F-A96F-4286-8DC0-9993E65D6A81	6	Libra 2
28/01/2022	8:00:00	009933B3-5DEB-44A3-9C8E-6FD7E8252B4D	162	Mira
28/03/2022	9:15:00	009933B3-5DEB-44A3-9C8E-6FD7E8252B4D	162	Mira
30/07/2022	12:59:00	009933B3-5DEB-44A3-9C8E-6FD7E8252B4D	162	Mira
3/10/2022	7:00:00	009933B3-5DEB-44A3-9C8E-6FD7E8252B4D	162	Mira
18/09/2023	14:40:00	00A0B131-D3EA-4FD9-8F58-4B5CDD4BC7D2	7	Libra 3
21/09/2023	7:51:00	00A0B131-D3EA-4FD9-8F58-4B5CDD4BC7D2	7	Libra 3
14/01/2022	9:30:00	00A32A4A-5F94-4B87-A435-FE8E945524B3	5	Libra 1
17/01/2022	10:27:00	00A32A4A-5F94-4B87-A435-FE8E945524B3	160	Vega
28/01/2022	13:40:00	00A32A4A-5F94-4B87-A435-FE8E945524B3	5	Libra 1
14/02/2022	15:50:00	00A32A4A-5F94-4B87-A435-FE8E945524B3	5	Libra 1
23/02/2022	11:05:00	00A32A4A-5F94-4B87-A435-FE8E945524B3	5	Libra 1
7/03/2022	16:15:00	00A32A4A-5F94-4B87-A435-FE8E945524B3	5	Libra 1
20/05/2022	15:15:00	00A32A4A-5F94-4B87-A435-FE8E945524B3	5	Libra 1
11/03/2022	15:20:00	00AD2094-51F1-4FA2-BA3C-88B2CB7676FD	20	De Zeilen 2
15/03/2022	17:09:00	00AD2094-51F1-4FA2-BA3C-88B2CB7676FD	20	De Zeilen 2
11/09/2023	15:40:00	00C12581-2D1B-4BB6-9E33-A94A6D0BA1D7	13	Sirius 2
20/05/2022	20:35:00	00C60BEE-CCBC-497C-A308-FB69C7D87003	9	Orion 2
16/01/2022	17:00:00	00EF73CC-6981-499B-8114-118728FBF8E1	7	Libra 3
19/01/2022	9:00:00	00EF73CC-6981-499B-8114-118728FBF8E1	7	Libra 3
9/08/2023	10:40:00	00EFAB2E-FF01-42C4-821C-FB08D5A8A559	150	Nova 1
16/08/2023	14:25:00	00EFAB2E-FF01-42C4-821C-FB08D5A8A559	150	Nova 1
30/06/2022	9:55:00	00FDF28C-BFFB-4879-A80B-2DB008FFEDB8	150	Nova 1
6/07/2022	10:00:00	00FDF28C-BFFB-4879-A80B-2DB008FFEDB8	150	Nova 1
14/07/2023	9:30:00	010E32D3-7238-465C-AFD5-7E9ECC6EE488	7	Libra 3
22/02/2022	23:00:00	01392978-247A-4A97-874D-31878C6C883F	10	Orion 3
24/08/2022	23:00:00	01392978-247A-4A97-874D-31878C6C883F	10	Orion 3
31/12/2022	7:00:00	01392978-247A-4A97-874D-31878C6C883F	10	Orion 3
14/01/2023	8:30:00	01392978-247A-4A97-874D-31878C6C883F	11	Orion 4
16/03/2023	8:50:00	013A64F7-63DD-4AA4-B3E2-7F60486D23D8	5	Libra 1
5/01/2022	12:53:00	016E9B85-8036-4B29-8166-909B6ACF0B99	14	Sirius 3
27/03/2022	21:05:00	017004E3-D917-4CC3-9F56-33BEA032D90C	150