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
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.
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!