Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Needs per month

Hello,     i really need your help, i am currently stuck on a problem for already 1 month I hope you can help me.   Here is my problem:   I have 1 requests which shows me my daily needs for e...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    I assumed now that M is the current month, M1 the next month etc. Therefore I created for you a custom function where you can achive this. Here the code that does the trick

    let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZNBEsQgCAT/4pkDoER9S2r//40Fc9gCMXswFW3RYZK579LqQCxQSEdDBGz2Uvuk8oEfZh0XIFJK6ypWnBfb6rCzR4plYa3O8aXDdOW0m3a9u6fUagSBJKXTalnPJk45rYfYhivfYHawdXY4wJaFOsjM1VNd8gFrrpDaakAvOHD5p9CWWb196ofOHR7e+43Px3yhFDM69zf8/FRQOcfsvs6Gmzd34/LaGlvnFRkO0k2yqG+H6uGN34zxcRlNXuISaYhLxCEuEYe4ROzjEqmPS6Q+LpHGuES+xWVt+HwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date de l'extraction" = _t, #"Code article" = _t, #"Quantitè requise" = _t, #"Date du besoin" = _t]),
    TransformToDate = Table.TransformColumns(Quelle,{{"Date de l'extraction", each Date.From(Number.From(_)), type date},{"Date du besoin", each Date.From(Number.From(_)), type date}}),
    ChangeType = Table.TransformColumnTypes(TransformToDate,{{"Quantitè requise", type number}}),
    CalculateDifferenceMonth = (datetocalculate as date) as number =>
    let
        Monthcalc = Date.Month(Date.From(datetocalculate)),
        Yearcalc = Date.Year(Date.From(datetocalculate)),
        Monthcurr = Date.Month( DateTime.FixedLocalNow()),
        Yearcurr = Date.Year(DateTime.FixedLocalNow()),

        MonthDiff =(Monthcalc-Monthcurr), // -
        YearDiffinMonths = ((Yearcurr-Yearcalc)*12),
        Result = MonthDiff  - YearDiffinMonths
    in
        Result,
    
    RecalculteDateDuBesoin =  Table.TransformColumns
    (
        ChangeType,
        {
            {"Date du besoin", each "M"&Text.From(CalculateDifferenceMonth(_)) }
        } 
    ),
    Pivot = Table.Pivot
    (
        RecalculteDateDuBesoin, 
        List.Distinct(RecalculteDateDuBesoin[#"Date du besoin"]), 
        "Date du besoin", 
        "Quantitè requise", 
        List.Sum
    )
in
    Pivot

     

    Copy paste this code to the advanced editor to see how the solution works

    If this post helps or solves your problem, please mark it as solution.
    Kudos are nice to - thanks
    Have fun

    Jimmy

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hello Jimmy801 ,

 

I don't know if I understood your questions very well.

 

For your first question: - This information comes directly from my computer system (SAP) in Excel format which I then incorporated into power BI. So I did not make any formula to be able to calculate and have these numbers.

 

 

Indeed, I wish to use the columne "Date du besoin" in order to transform my column into M-1 M-2, M, M + 1, M + 2 ....

 

 

It's normal that the numbers are very high, since the information comes from my computer system, don't worry 🙂

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello Anonymous 

 

I assumed now that M is the current month, M1 the next month etc. Therefore I created for you a custom function where you can achive this. Here the code that does the trick

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZNBEsQgCAT/4pkDoER9S2r//40Fc9gCMXswFW3RYZK579LqQCxQSEdDBGz2Uvuk8oEfZh0XIFJK6ypWnBfb6rCzR4plYa3O8aXDdOW0m3a9u6fUagSBJKXTalnPJk45rYfYhivfYHawdXY4wJaFOsjM1VNd8gFrrpDaakAvOHD5p9CWWb196ofOHR7e+43Px3yhFDM69zf8/FRQOcfsvs6Gmzd34/LaGlvnFRkO0k2yqG+H6uGN34zxcRlNXuISaYhLxCEuEYe4ROzjEqmPS6Q+LpHGuES+xWVt+HwB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date de l'extraction" = _t, #"Code article" = _t, #"Quantitè requise" = _t, #"Date du besoin" = _t]),
    TransformToDate = Table.TransformColumns(Quelle,{{"Date de l'extraction", each Date.From(Number.From(_)), type date},{"Date du besoin", each Date.From(Number.From(_)), type date}}),
    ChangeType = Table.TransformColumnTypes(TransformToDate,{{"Quantitè requise", type number}}),
    CalculateDifferenceMonth = (datetocalculate as date) as number =>
    let
        Monthcalc = Date.Month(Date.From(datetocalculate)),
        Yearcalc = Date.Year(Date.From(datetocalculate)),
        Monthcurr = Date.Month( DateTime.FixedLocalNow()),
        Yearcurr = Date.Year(DateTime.FixedLocalNow()),

        MonthDiff =(Monthcalc-Monthcurr), // -
        YearDiffinMonths = ((Yearcurr-Yearcalc)*12),
        Result = MonthDiff  - YearDiffinMonths
    in
        Result,
    
    RecalculteDateDuBesoin =  Table.TransformColumns
    (
        ChangeType,
        {
            {"Date du besoin", each "M"&Text.From(CalculateDifferenceMonth(_)) }
        } 
    ),
    Pivot = Table.Pivot
    (
        RecalculteDateDuBesoin, 
        List.Distinct(RecalculteDateDuBesoin[#"Date du besoin"]), 
        "Date du besoin", 
        "Quantitè requise", 
        List.Sum
    )
in
    Pivot

 

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy