Problems with format dates entered

[Eddistoff]

Hi everyone, 

I need your assistance please, I am barely new using PBI and I have a situation with dates. There is a template of excel fulfilled by people who manage a process, so I required to get the amount of days it takes to approve a process. The problem is they have to enter manually the date, some of them entered the date in a format which is not correct, and others do it correctly.

This is an example I created, so I have I tried to solve it with Power BI, but with no succees. I finally do it manually everytime I require to update the report, just by adding a column with difference of days which result should not take longer than a month:

Date of registration	Product	Date of approval	Comments	Format entered
04/05/2022	1	05/05/2022	Correct	DD-MM-YYYY
09/08/2022	2	08/10/2022	Wrong	MM-DD-YYYY
04/08/2022	3	09/04/2022	Wrong	MM-DD-YYYY
01/02/2022	4	05/02/2022	Correct	DD-MM-YYYY
09/05/2022	5	05/09/2022	Wrong	MM-DD-YYYY
09/07/2022	6	10/07/2022	Correct	DD-MM-YYYY
03/05/2022	7	05/10/2022	Wrong	MM-DD-YYYY
13/02/2022	8	14/02/2022	Correct	DD-MM-YYYY
21/04/2022	9	22/04/2022	Correct	DD-MM-YYYY

Example of manual solution for first example:

Date of approval - date of registration = amount of days 

05-05-2022 - 04-05-2022 = 1

Thanks in advance!!

Edgardo Y.

[Eddistoff]

Thanks a lot for yout help!!!!

[BA_Pete · Accepted solution]

Hi @Eddistoff ,

Add this as a new custom column to restate the approval date in the correct format:

let
    __cDate = Date.AddMonths(Date.From([Date of registration]), 1)
in
    if Date.From([Date of approval]) < Date.From([Date of registration]) or Date.From([Date of approval]) > __cDate
    then Text.Combine(
        {
            Text.Middle([Date of approval], 3, 2),
            Text.Start([Date of approval], 2),
            Text.End([Date of approval], 4)
        },
        "/"
    )
    else [Date of approval]

From here you can change the data type of your new column to Date Type and perform your calculation.

Example as working query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBBC8IwDIX/S88bTdLMrWd33Vlk7CTizUHx/+Mrri4HpSuENo98fUnm2ZF66ryQiGscI5DtwnlN6X574TWO7TS1Vxy3NMCip6FU5UDGVIRLWp8P3CDA7ZAaKGQIv2gNYk9SanRrUA41+B2j27BY8wLUl5pTXggZ4b9XMF79x6u6DA5mjCF76ZG5hM3KYl6+GOE3trwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of registration" = _t, Product = _t, #"Date of approval" = _t, #"Comments " = _t, #"Format entered" = _t]),
    chgRegDateType = Table.TransformColumnTypes(Source,{{"Date of registration", type date}}),
    addDtApproval =
    Table.AddColumn(
        chgRegDateType,
        "dtApproval", each
        let
            __cDate = Date.AddMonths(Date.From([Date of registration]), 1)
        in
        if Date.From([Date of approval]) < Date.From([Date of registration]) or Date.From([Date of approval]) > __cDate
        then Text.Combine(
            {
                Text.Middle([Date of approval], 3, 2),
                Text.Start([Date of approval], 2),
                Text.End([Date of approval], 4)
            },
            "/"
        )
        else [Date of approval]
    )
in
    addDtApproval

Working query output:

Pete