cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Converting SQL formula to DAX

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!

2 REPLIES 2
Solution Sage

@ariam Hi! Can you paste some sample data and the formula? also try to explain the expected output on the data you'll paste.

New Member

The output should look something like this, (1-4 are quarters 1-4, and total is annual total) - Basically, if a new account is opened in a given year and quarter it needs to be added in, and they also want it to include accounts that have continued from previous quarters (even if its from a previous year) if it hasnt been closed yet. I can't share the data, but it is using 'date opened' and 'date closed' columns and just counting and adding the records opened and not closed during the filters dates. I hope this makes sense.