Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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
Proud to be a Datanaut!
Thanks a lot for yout help!!!!
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
Proud to be a Datanaut!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
72 | |
67 | |
42 | |
28 | |
21 |