Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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
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
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 🙂
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
51 | |
19 | |
12 | |
11 |