Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good afternoon: I'm a new one in Power BI and I have to solve the next situation that comes to me with more than one complexity.
On the one hand I have an Excel of historical data ranging from 2014 to 2018. In it I have 3 columns related to Detail (Commitments not fulfilled), Year and Quantity.
On the other hand, the corresponding data from 2019 to the present day, arise from another Excel that is being updated and the quantities must be calculated automatically.
1) The first problem arises in the calculation of quantities: During each year the number of commitments not fulfilled is updated each month and should always take the value corresponding to the last date until the end of the year. Example: for 2019 you must report the value of the last day of 2019 (e.g. 31/12/2019). Then, during 2020, you must take the amount corresponding to the last date until December 2020 where you must take the value corresponding to December 31, 2020 and so on for the rest of the future years. That is, what is needed is that from 2019 onwards, the amount is the last of each year. In 2019 it will be December and in the current year, it will be the last measurement date until the end of the year. The quantity to be displayed is never cumulative, but is the quantity existing to the measurement of the last date.
2) The second problem is that I must join the two tables (historical data + calculated data) into a single array. This matrix must be updated and incorporated automatically from 2019 onwards. In 2019 you will need to show the data for the last date of 2019. By 2020, as the year goes on, you will need to display the values of the last measurement date until you reach December 2020 and you will need to display the December 2020 data. Then in 2021, you will need to display the data for the last date of the year as the months go by and so on.
How can I resolve these two issues?
Thanks a lot!
Hi @belmassiccioni,
#1, Dax formula is possible to extract the current data, then you can use this as a condition to dynamically defined the last date based on the current value.
#2, How did the date range you mean? Based on the maximum date of records? If that is a case, you can consider creating a calculated table to extract the min and max date from your history records table and summarize filtered records to a new table.
BTW, if you share some dummy data with raw table structure and expected result, it will help us to clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Thanks a lot.
For point 1, the table is as follows. The column for the calculation is the so-called analysis date. Unpaid commitments are the sum of the Pending and Pending Statement out of time.
Code | State | Commitment Closing Date | Date of discharge | date analysis |
#0001 | Compliment | 05-nov-14 | 31-Dec-19 | 31-Dec-19 |
#0019 | Closed | 13-jul-15 | 31-Dec-19 | 31-Dec-19 |
#0359 | Slope | 31/07/2020 | 31/07/2020 | |
#0365 | Pending Out-of-Term | 31/07/2020 | 31/07/2020 | |
#0359 | Slope | 31/07/2020 | 30-ago-20 | |
#0365 | Pending Out-of-Term | 31/07/2020 | 30-ago-20 | |
#0417 | Compliment | 04-ago-20 | 31/07/2020 | 30-ago-20 |
#0690 | Closed | 16-sep-20 | 31/07/2020 | 29-sep-20 |
#0690 | Compliment | 16-sep-20 | 31/07/2020 | 29-sep-20 |
#0374 | Pending Out-of-Term | 31/07/2020 | 29-sep-20 | |
#0375 | Slope | 31/07/2020 | 29-sep-20 | |
#0362 | Closed | 01-oct-20 | 31-jul-20 | 02-oct-20 |
#0376 | Slope | 31-jul-20 | 02-oct-20 | |
#0377 | Pending Out-of-Term | 31-jul-20 | 02-oct-20 | |
#0469 | Compliment | 01-oct-20 | 31-jul-20 | 02-oct-20 |
#0472 | Compliment | 01-oct-20 | 31-jul-20 | 02-oct-20 |
For point 2, you must join the data that arises by calculation of the previous table from 2019 onwards and that of historical data (from 2014 to 2018)
Detail | 2014 | 2015 | 2016 | 2017 | 2018 |
Number of commitments fulfilled | 21 | 56 | 53 | 45 | 60 |
Number of commitments closed | 0 | 26 | 21 | 6 | 10 |
Number of commitments not fulfilled | 19 | 44 | 34 | 55 | 63 |
Total | 40 | 126 | 108 | 106 | 133 |
The expected result is:
Detail | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 |
Number of commitments fulfilled | 21 | 56 | 53 | 45 | 60 | 24 | 17 |
Number of commitments closed | 0 | 26 | 21 | 6 | 10 | 36 | 69 |
Number of commitments not fulfilled | 19 | 44 | 34 | 55 | 63 | 89 | 135 |
Total | 40 | 126 | 108 | 106 | 133 | 149 | 221 |
Data from 2014 to 2018 comes from the historical table. From 2019 onwards, of automatic calculations considering the last feche of measurement in each year.
Thanks a lot
For point 1, the table is as follows. The column for the calculation is the so-called analysis date. Unpaid commitments are the sum of the Pending and Pending Statement out of time.
Code | State | Commitment Closing Date | Date of discharge | date analysis |
#0001 | Compliment | 05-nov-14 | 31-Dec-19 | 31-Dec-19 |
#0019 | Closed | 13-jul-15 | 31-Dec-19 | 31-Dec-19 |
#0359 | Slope | 31/07/2020 | 31/07/2020 | |
#0365 | Pending Out-of-Term | 31/07/2020 | 31/07/2020 | |
#0359 | Slope | 31/07/2020 | 30-ago-20 | |
#0365 | Pending Out-of-Term | 31/07/2020 | 30-ago-20 | |
#0417 | Compliment | 04-ago-20 | 31/07/2020 | 30-ago-20 |
#0690 | Closed | 16-sep-20 | 31/07/2020 | 29-sep-20 |
#0690 | Compliment | 16-sep-20 | 31/07/2020 | 29-sep-20 |
#0374 | Pending Out-of-Term | 31/07/2020 | 29-sep-20 | |
#0375 | Slope | 31/07/2020 | 29-sep-20 | |
#0362 | Closed | 01-oct-20 | 31-jul-20 | 02-oct-20 |
#0376 | Slope | 31-jul-20 | 02-oct-20 | |
#0377 | Pending Out-of-Term | 31-jul-20 | 02-oct-20 | |
#0469 | Compliment | 01-oct-20 | 31-jul-20 | 02-oct-20 |
#0472 | Compliment | 01-oct-20 | 31-jul-20 | 02-oct-20 |
The expected result is:
Detail | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 |
Number of commitments fulfilled | 21 | 56 | 53 | 45 | 60 | 24 | 17 |
Number of commitments closed | 0 | 26 | 21 | 6 | 10 | 36 | 69 |
Number of commitments not fulfilled | 19 | 44 | 34 | 55 | 63 | 89 | 135 |
Total | 40 | 126 | 108 | 106 | 133 | 149 | 221 |
Data from 2014 to 2018 comes from the historical table. From 2019 onwards, of automatic calculations considering the last feche of measurement in each year.
Thanks a lot
For point 2, you must join the data that arises by calculation of the previous table from 2019 onwards and that of historical data (from 2014 to 2018)
Detail | 2014 | 2015 | 2016 | 2017 | 2018 |
Number of commitments fulfilled | 21 | 56 | 53 | 45 | 60 |
Number of commitments closed | 0 | 26 | 21 | 6 | 10 |
Number of commitments not fulfilled | 19 | 44 | 34 | 55 | 63 |
Total | 40 | 126 | 108 | 106 | 133 |
provide sample data in usable format and show expected outcome.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |