Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Based on the first 3 columns(Image Attached), I am calculating Backlog for each day in excel, now I am moving to Power BI and new here.
Using DAX how can I calculate Backlog from the first 3 columns in Power BI.
I have Incident ID, Created Date and Closed Date column , and I want Backlog of each day.
Kindly suggest
Thankyou
Can you please help me with this we you have the solution?
Hi @rohitchouhan ,
Backlog =
var ClosedTckts = CALCULATE(COUNT('Table'[Close Date]),USERELATIONSHIP('Date'[Date],'Table'[Close Date]))
var OpenTckts= CALCULATE(COUNT('Table'[Open Date]),USERELATIONSHIP('Date'[Date],'Table'[Open Date]))
var Difference = ClosedTckts - OpenTckts
var Cumulative = FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date]))
var Backlog = CALCULATE(
SUMX('Date', Difference),
Cumulative)
return
Backlog
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@harshnathani Hello sir, thanks for reply
what to to put here in place of ('Date'[Date],
kindly help
Hi @rohitchouhan ,
Assuming that you have a Date Table.
Can you share some sample data and your Data Model
Regards,
Harsh Nathani
I don't have a date table there,I just have created a date and closed date for tickets and a unique Incident ID.
My data source is excel here, which i am sharing here.
Assuming you have a Date table (e.g., with your Date Index from visual), you could try an expression like this to get the # of open items on any given day (in a table visual with your 'Date'[Date] column).
Open Incidents =
VAR __thisdate =
MIN ( 'Date'[Date] ) //assuming you have one date in context min, max, sum won't matter
RETURN
CALCULATE (
COUNTROWS ( Table ),
ALL ( Table ),
Table[Created Date] <= __thisdate,
Table[Closed Date] > __thisdate
)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat this is not working.
my input data is first 3 column and result I want is the last column(Backlog).
also can you temm how to add date index through DAX
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
69 | |
36 | |
21 | |
17 | |
15 |
User | Count |
---|---|
126 | |
30 | |
28 | |
24 | |
23 |