Power Query date Interval IF

[Juju-PowerBI]

Hello,

STARTING END DATA
02/01/2020 19/12/2020 20
12/07/2020 19/12/2020 40
19/12/2020 19/12/2020 50
01/01/2020 07/07/2020 20
15/02/2019 01/07/2020 50
15/02/2019 01/10/2020 50

I would like to create a quarter formula according to the following example in power query

do you think this is possible?

Thanks

[Juju-PowerBI]

STARTING END DATA
02/01/2020 19/12/2020 20
12/07/2020 19/12/2020 40
19/12/2020 19/12/2020 50
01/01/2020 07/07/2020 20
15/02/2019 01/07/2020 50
15/02/2019 01/10/2020 50

Thanks,

Now i would like to do like this new example,

How do u think with these new columns ?

[Anonymous]

let

    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUTK01Dc00sfGMTVQitVBqDPSNzAnRh2x5pnqG4CkDC2BHANDJMNB6mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [year = _t, START = _t, REALIZATION = _t, data = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"year", Int64.Type}, {"START", type date}, {"REALIZATION", type date}, {"data", Int64.Type}}),
    f={"TR1","TR2","TR3","TR4"},
    quarter=(start)=> List.Transform({1..4},each  if Date.QuarterOfYear(start)>_ then "no" else 0 ),
    
    ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(quarter(_[START]),f)&Record.FromList({Text.From(_[data])},{f{Date.QuarterOfYear(_[REALIZATION])-1}})))
in
    ttr

[Juju-PowerBI]

Hi @Anonymous 

Thanks for your solution, do you if it is possible to add also on this syntax the concept of Q1 Q2 and Q3 like the new example?

Thanks

[Anonymous]

I can try to do that.

as i forgot about the problem a bit, you should upload an example of the source table and the desired table so that i can easily copy it.

[Juju-PowerBI]

STARTING       ENDING           DATA
02/01/2020     19/12/2020      20
12/07/2020     19/12/2020      40
19/12/2020     19/12/2020      50
01/01/2020      07/07/2020     20
15/02/2019      01/07/2020     50
15/02/2019      01/10/2020     50

Hi @Anonymous 

I would like to achieve this result, thanks

[Anonymous]

In this case, the greatest difficulty is understanding what is required. I know little English, but in my working context the letter Q indicates the fourth of the year, that is a duration of 3 months. Here, if I interpret the clues you provide correctly, by Q you mean a duration of 4 months. In this sense I have modified the script.

let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc3BCcAwDAPAXfwOWDY1JbOY7JZZMlkdEqhLab/iJLkTlCGsUIxOhUaXyqJ3oKBWnCLC+cOOzZ7pi9li8ZhPY3mPp0NjzKLURSQR+yKCTNoF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, end = _t, data = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"start", type date}, {"end", type date}, {"data", Int64.Type}}),
    f={"Q1","Q2","Q3"},
    third=(start)=> List.Transform({1..3},each  if ThirdOfYear(start)>_ then "no" else 0 ),
    ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(third(_[start]),f)&Record.FromList({_[data]},{f{ThirdOfYear(_[end])-1}})))
in
    ttr

[Juju-PowerBI]

Hi @Anonymous 

Thanks you for the script, but it does not look to work or i haven't understood

Where do you put the ThirdOfYear ?

I'm trying to have in the same table the Trimester = 3 months, like the first script and also the Qarter = 4 months

[Anonymous · Accepted solution]

try this (last attempt)

let
     Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc3BCcAwDAPAXfwOWDY1JbOY7JZZMlkdEqhLab/iJLkTlCGsUIxOhUaXyqJ3oKBWnCLC+cOOzZ7pi9li8ZhPY3mPp0NjzKLURSQR+yKCTNoF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [start = _t, end = _t, data = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"start", type date}, {"end", type date}, {"data", Int64.Type}}),
    f={"Q1","Q2","Q3"},
    ThirdOfYear=(ymd) => Number.IntegerDivide(Date.Month(ymd)+3,4),
    third=(start)=> List.Transform({1..3},each  if ThirdOfYear(start)>_ then "no" else 0 ),
    ttr=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(third(_[start]),f)&Record.FromList({_[data]},{f{ThirdOfYear(_[end])-1}}))),
    ttrid=Table.AddIndexColumn(ttr,"id",1),
    ft={"TR1","TR2","TR3","TR4"},
    quarter=(start)=> List.Transform({1..4},each  if Date.QuarterOfYear(start)>_ then "no" else 0 ),    
    ttrt=Table.FromRecords(Table.TransformRows(#"Modificato tipo", each _&Record.FromList(quarter(_[start]),ft)&Record.FromList({Text.From(_[data])},{ft{Date.QuarterOfYear(_[end])-1}}))),
    ttrtid=Table.AddIndexColumn(ttrt,"id",1),
    join= Table.NestedJoin(ttrtid, {"id"}, ttrid, {"id"}, "QandT", JoinKind.Inner)
    
    in   Table.ExpandTableColumn(join, "QandT", {"Q1", "Q2", "Q3"}, {"Q1", "Q2", "Q3"})

[StefanoGrimaldi]

according to how you presenting what you want this its added columns to the existing table so will go like this: 

Column 1 => 

TR1 = IF(

QUARTER('Table'[START])=1,

IF( QUARTER('Table'[REALIZATION])=1,CONVERT('Table'[data],STRING),

"0"),"no")

column 2 => 

TR2 = IF(

QUARTER('Table'[START])<=2,

IF( QUARTER('Table'[REALIZATION])=2,CONVERT('Table'[data],STRING),

"0"),"no")

column 3 => 

TR3 = IF(

QUARTER('Table'[START])<=3,

IF( QUARTER('Table'[REALIZATION])=3,CONVERT('Table'[data],STRING),

"0"),"no")

column 4 =>

TR4 = IF(

QUARTER('Table'[START])<=4,

IF( QUARTER('Table'[REALIZATION])=4,CONVERT('Table'[data],STRING),

"0"),"no")

if this solved your question give some kudos and mark as solution for others to find it easily, thanks. 

[Juju-PowerBI]

Bonjour @StefanoGrimaldi 

Pouriez vous montrer la formule ? en essayant cela na pas fonctionné

Merci

[StefanoGrimaldi]

hey, I paste the dax formula of it if you want to add it to power query you will have to adapt the syntyx example: 

TR1 =

if Date.QuarterOfYear(start date) = 1  then IF Date.QuarterOfYear(realization date)=1 then [data] else 

"0" else "no"

something like that, before this step you need to make the data column a text type for the M code to work here. them after this step you can make back the data column to value.