Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a matrix table that is broken into 4 quarters using a 'date opened - year' filter on the page, and I need it to output a running total of values from previous years as well and exclude anything that has already been closed. This is the formula that a coworker wrote for when we had this on SQL (this section is just for Quarter 1 but he repeated it for each quarter):
-- New and Continued Case Totals
SELECT
'Total number of cases (new and continued) for current quarter' AS 'CaseType',
(SELECT COUNT(CaseNumber) FROM temp_Continued_Q1a WHERE (YEAR(DateOpened) = caseYear) AND (MONTH(DateOpened) = 1 OR MONTH(DateOpened) = 2 OR MONTH(DateOpened) = 3) AND (DateOpened <= CURDATE())) +
(SELECT COUNT(CaseNumber) FROM temp_Continued_Q1b WHERE (DateOpened < CONCAT(caseYear, '-01-01') AND DateClosed IS NULL) AND (CURDATE() >= CONCAT(caseYear, '-01-01')) OR (DateOpened < CONCAT(caseYear, '-01-01') AND DateClosed >= CONCAT(caseYear, '-01-01')) AND (CURDATE() >= CONCAT(caseYear, '-01-01'))) AS Q1
I have been trying for weeks to convert this into a DAX measure that will give me the output that I need. I tried breaking it into several measures and am able to get the first part by just doing a distinct count of the case numbers and throwing it into the matrix table, but the second part for the 'continuing' cases I cannot seem to get. I really hope someone can help me!
Solved! Go to Solution.
Nevermind - I figured out the problem I was having, and converting the SQL code was the wrong way to go in this case as it didn't give me my desired output. This is the code I needed:
Total Records Opened Before and Closed On or After Filtered Year (Including Null Closed Dates) =
CALCULATE(
COUNTROWS(YourTableName),
ALL(YourTableName[YearColumn]),
(
ISBLANK(YourTableName[ClosedDate]) ||
YEAR(YourTableName[ClosedDate]) >= SELECTEDVALUE(YourTableName[YearColumn])
),
YEAR(YourTableName[OpenedDate]) <= SELECTEDVALUE(YourTableName[YearColumn])
)
Nevermind - I figured out the problem I was having, and converting the SQL code was the wrong way to go in this case as it didn't give me my desired output. This is the code I needed:
Total Records Opened Before and Closed On or After Filtered Year (Including Null Closed Dates) =
CALCULATE(
COUNTROWS(YourTableName),
ALL(YourTableName[YearColumn]),
(
ISBLANK(YourTableName[ClosedDate]) ||
YEAR(YourTableName[ClosedDate]) >= SELECTEDVALUE(YourTableName[YearColumn])
),
YEAR(YourTableName[OpenedDate]) <= SELECTEDVALUE(YourTableName[YearColumn])
)
@ariam Hi! Can you paste some sample data and the formula? also try to explain the expected output on the data you'll paste.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |