Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ariam
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
BeaBF
Solution Sage
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.

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.

ariam_1-1720624431317.png

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.