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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
maart666
Helper I
Helper I

Calculation of the backlog based on the data from the previous lines

Hi,

I need to calculate the number of rows, for dates from the "PLAN-FINISH" column that is less than PLAN-FINISH dates from the table gropuped by mohths (name of mohths are in Polish):
1.PNG

I need to count the rows for which the "ACTUAL-FINISH" date is later than month of "PLAN-FINISH" or empty.
This is the filtering example for January 2022:
2.png

Link to a sample pbix file

https://www.dropbox.com/s/edd4ylo40qskyvx/OD.pbix?dl=0

 

Thank you in advance for your help.
Greetings
Marcin



 

6 REPLIES 6
Whitewater100
Solution Sage
Solution Sage

Hi:

I checked it again and noticed you have mutltiple months on both sides for comparison.

 

I have calulated Late with both filters (Act Fin & Plan Fin). 

 

What exactly are you filtering on? You have many choices with plan start, plan finish, actual finish and open plans. There is only one column to connect the date table too, which is plan finish.

 

Here is the best I can do at this point:

https://drive.google.com/file/d/1i2NdhvzxmkVB9Ur1xjcOhxCGli2x6H79/view?usp=sharing 

 

Hello Again:

 

If you could place a unique plan id number or index for each separate plan, it will be very benefical. Thank you.

Whitewater100
Solution Sage
Solution Sage

Hi Marcin:

 

Please see attached file. I included late plans and late and unfinished plans.

https://drive.google.com/file/d/1BWvwgW87pZhlv0IH1UT3TGJiM8SJd0Hu/view?usp=sharing 

Thank you Whitewater100,

 

but unfortunatly it is not what I need to calcualte.

I need to count all the rows from the previous periods for which ACTL-FINISH is later than PLAN-FINISH, or ACTL-FINISH is empty.
For JAN it need to be 15 like on ths filter:Jan.PNG

 For February it is 20:

Feb.PNG

 

Thank you.

 

Hello:

I understand you want cumulative total.  Becasue you said "ACTUAL-FINISH" date is later than month of "PLAN-FINISH" or empty, I come up with 10 for January 2022 and 20 for Fenbruary. There are some late plans but they finish in the same month as planned, but are late, but are not counted. Is this still the logic? I hope this is what you need:-)

https://drive.google.com/file/d/1BWvwgW87pZhlv0IH1UT3TGJiM8SJd0Hu/view?usp=sharing 

Thank you very much Whitewater100 for your afford, but it is not what I need to calculate.
I need tabele showing those values:

January15
February20
March18

 

These are the counted numbers of rows for this filtering (this is example for January):
1.PNG

 

2.PNG

 

If anyone has an idea how to do it I would be grateful.
Thank you.

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors