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
pratafran
Helper III
Helper III

Backlog evolution at month end

Hello Comunity!

 

I'm trying to figure out how to calculate the backlog of documents created (invoices) that still were not submitted to customer at month end and see it in a chart to monitor the evolution month to month BUT also be able to select any month and see in a table the list of invoices that are part of that backlog.

 

My current solution is to do it in excel with formulas generateing the "backlog database" for each month, and appending each month on a long database with the corresponding backlog month column.

 

The logic for each month end (e.g. 31/Jan/2010) is:

If the document was already submitted (Status: Y), it is backlog at month end if it was invoiced before or in the backlog date and submitted after that date.

If the document was still not submitted (Status: N), it is backlog at month end if it was invoiced before or in the backlog date.

 

Data:

Invoice Number

Invoice Volume

Invoice Date

Submission Date (never is black, if not submitted it has the estimated submission date, otherwise, the actual one)

Status (Y= Submitted, N=Not submitted)

 

Example in excel attached. My desired solution would be to avoid appending and get it with calculations but I'm not being able to reach a solution. Second option is maybe replicate the process of append in a Power Query but I do not know how to do it.

 

Thanks in advance!

1 ACCEPTED SOLUTION

You can create a formula like this after having a date table

backlog = CALCULATE(COUNTx(filter(Sheet1,(Sheet1[Invoice Date] <=MAX('Date'[Date]) && Sheet1[Submission Date]>=MAX('Date'[Date]))),Sheet1[Invoice Number]),CROSSFILTER('Date'[Date],Sheet1[Invoice Date],None))

backlog = CALCULATE(COUNTx(filter(Sheet1,(Sheet1[Invoice Date] <=MAX('Date'[Date]) && Sheet1[Submission Date]>=MAX('Date'[Date]))),Sheet1[Invoice Number]),CROSSFILTER('Date'[Date],Sheet1[Invoice Date],None))+0

 

with ), you have control display of month

 

You can build yes and no on top of it

 

backlog status = if([backlog]=1,"Y","N")

Link of solution:https://www.dropbox.com/s/d7vvbk4zekhsx5t/backlog.pbix?dl=0

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

 

View solution in original post

8 REPLIES 8
v-eachen-msft
Community Support
Community Support

Hi @pratafran ,

 

You could create a data dimension based on your "Backlog Month".

Then refer to the following measure:

Measure =
IF (
    SELECTEDVALUE ( 'Table 2'[Backlog Month] )
        IN CALCULATETABLE (
            DISTINCT ( 'Table'[Backlog Month] ),
            ALLEXCEPT ( 'Table', 'Table'[Invoice Number] )
        ),
    "Y",
    "N"
)

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

It is a different approach to the above solution but seems to solve the problem too!, I will explore a little more to understand it better but thank you very much for your contribution!

amitchandak
Super User
Super User

I did not find the excel link.

Thanks for letting me know. I have edited the post to include it.

You can create a formula like this after having a date table

backlog = CALCULATE(COUNTx(filter(Sheet1,(Sheet1[Invoice Date] <=MAX('Date'[Date]) && Sheet1[Submission Date]>=MAX('Date'[Date]))),Sheet1[Invoice Number]),CROSSFILTER('Date'[Date],Sheet1[Invoice Date],None))

backlog = CALCULATE(COUNTx(filter(Sheet1,(Sheet1[Invoice Date] <=MAX('Date'[Date]) && Sheet1[Submission Date]>=MAX('Date'[Date]))),Sheet1[Invoice Number]),CROSSFILTER('Date'[Date],Sheet1[Invoice Date],None))+0

 

with ), you have control display of month

 

You can build yes and no on top of it

 

backlog status = if([backlog]=1,"Y","N")

Link of solution:https://www.dropbox.com/s/d7vvbk4zekhsx5t/backlog.pbix?dl=0

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

 

@amitchandak When trying to import my real data, I found the following error:

 

Sheet1
Column 'Submission Date' in Table 'Sheet1' contains a duplicate value '10/17/2018' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.
 
Both, Invoice Dates and Submission dates can have duplicated values (but never more than 1 value per invoice number).
 
I think that this error has to do with the joint 1to1 between the two tables (fields Date and Submission Date) but I dont undertand what this joint is used for.
 
Can you help me to figure it out?
 
Thanks in advance!

Double click on relation line and make relation Date table to invoice table 1 to Many and single direction and check.

 

When power bi do not see the duplicate date it create 1-1 relation.

It works!!, thank you very much for your help!! 🙂

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.