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

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago

Hello Anonymous 

 

what you miss to say is how these numbers of the column are calculated.

I assume that using the current date the column "Date du besoin" has to be transformed in M-1 M-2, M, M+1 etc. and then pivoted using the Column Quantitè requise. But the numbers of your result table seem very high. Without this information i cannot present any solution

 

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 

       

      okay, so I got you right.

      Then could you please tell me what the criteria for M, M-1 is... M= currentmonth = 1.12 - 31.12 or is it from now - 31 days. I need the exact specification. Please past also some example data in order to create a real example with your data without typing everything

       

      Jimmy

    • dax's avatar
      dax
      Community Support
      6 years ago

      Hi Edik01,

      I dn't understand your logic, if possible, could you please inform me the logic of M, M-1's value(you could use sample data to explain this, you could use simple sample data instead of real data)? Then I will help you more correctly.

      Please do mask sensitive data before uploading.

      Thanks for your understanding and support.
      Best Regards,
      Zoe Zhi

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

    • 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