Needs per month

[Anonymous]

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 each of my codes, and I would like to create columns (in dax or in M ​​language) which shows me the needs for M, M-1, M-2, M +1, M + 2, M + 3 -> up to M + 12.

Here is a screenshot of what I currently have (daily needs for each item code):

here is the result of what i would like:

[Jimmy801]

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]

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 · Accepted solution]

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

[dax]

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]

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