Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Number of last date each year and Union 2 tables: historical with updated autometic data

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!

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

CodeStateCommitment Closing DateDate of dischargedate analysis
#0001Compliment 05-nov-1431-Dec-1931-Dec-19
#0019Closed13-jul-1531-Dec-1931-Dec-19
#0359Slope 31/07/202031/07/2020
#0365Pending Out-of-Term 31/07/202031/07/2020
#0359Slope 31/07/202030-ago-20
#0365Pending Out-of-Term 31/07/202030-ago-20
#0417Compliment 04-ago-2031/07/202030-ago-20
#0690Closed16-sep-2031/07/202029-sep-20
#0690Compliment 16-sep-2031/07/202029-sep-20
#0374Pending Out-of-Term 31/07/202029-sep-20
#0375Slope 31/07/202029-sep-20
#0362Closed01-oct-2031-jul-2002-oct-20
#0376Slope 31-jul-2002-oct-20
#0377Pending Out-of-Term 31-jul-2002-oct-20
#0469Compliment 01-oct-2031-jul-2002-oct-20
#0472Compliment 01-oct-2031-jul-2002-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)

Detail20142015201620172018
Number of commitments fulfilled2156534560
Number of commitments closed02621610
Number of commitments not fulfilled 1944345563
Total40126108106

133

The expected result is:

Detail2014201520162017201820192020
Number of commitments fulfilled21565345602417
Number of commitments closed026216103669
Number of commitments not fulfilled 194434556389135
Total40126108106133149221

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.

CodeStateCommitment Closing DateDate of dischargedate analysis
#0001Compliment 05-nov-1431-Dec-1931-Dec-19
#0019Closed13-jul-1531-Dec-1931-Dec-19
#0359Slope 31/07/202031/07/2020
#0365Pending Out-of-Term 31/07/202031/07/2020
#0359Slope 31/07/202030-ago-20
#0365Pending Out-of-Term 31/07/202030-ago-20
#0417Compliment 04-ago-2031/07/202030-ago-20
#0690Closed16-sep-2031/07/202029-sep-20
#0690Compliment 16-sep-2031/07/202029-sep-20
#0374Pending Out-of-Term 31/07/202029-sep-20
#0375Slope 31/07/202029-sep-20
#0362Closed01-oct-2031-jul-2002-oct-20
#0376Slope 31-jul-2002-oct-20
#0377Pending Out-of-Term 31-jul-2002-oct-20
#0469Compliment 01-oct-2031-jul-2002-oct-20
#0472Compliment 01-oct-2031-jul-2002-oct-20

The expected result is:

Detail2014201520162017201820192020
Number of commitments fulfilled21565345602417
Number of commitments closed026216103669
Number of commitments not fulfilled 194434556389135
Total40126108106133149221

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)

Detail20142015201620172018
Number of commitments fulfilled2156534560
Number of commitments closed02621610
Number of commitments not fulfilled 1944345563
Total40126108106133
lbendlin
Super User
Super User

provide sample data in usable format and show expected outcome.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.