Forum Discussion
Backlog evolution at month end
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))+0with ), 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
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))+0with ), 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 LinkedinIt works!!, thank you very much for your help!! 🙂
amitchandak When trying to import my real data, I found the following error:
Sheet1Column '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.