DAX equivalent of GROUP BY and MIN - How do I count products launched, by year?
I want to create a measure that counts how many products were sold for the first time in a given year. That means if the product was sold in any year prior, it should not count in the current year. I would expect to do something like this: (code can be run and iterated here: [https://dax.do/fDTAmTl3kDIMaF/][1] ) DEFINE MEASURE 'Sales'[m1] = CALCULATE ( DISTINCTCOUNT (Sales[ProductKey]), SUMMARIZECOLUMNS ( Sales[ProductKey], CALCULATETABLE ( Sales, ALL( Sales[Order Date] ), ALL( 'Date'[Date] ), ALL( 'Date'[Calendar Year] ) ), "earliest_sale", MIN( Sales[Order Date] ) ), ALL( Sales[Order Date] ), ALL( 'Date'[Date] ) ) EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], "number of products sold for the first time", 'Sales'[m1] ) However, this returns the following: Which is exactly the same result as the one I get from EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], 'Sales', "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ), "total sales", COUNTROWS ( 'Sales' ) ) Finally, going perhaps, a little bit crazy, I tried this: EVALUATE SUMMARIZECOLUMNS ( 'Date'[Calendar Year], FILTER( 'Sales', Sales[Order Date] = CALCULATE( MIN( Sales[Order Date] ), SUMMARIZE ( CALCULATETABLE ( 'Sales', ALL ( Sales[Delivery Date] ), ALL ( 'Date'[Date] ), ALL ( 'Date'[Calendar Year] ) ), Sales[ProductKey] ), ALL ( Sales[Delivery Date] ), ALL ( 'Date'[Date] ), ALL ( 'Date'[Calendar Year] ) ) ), "distinct product sales", DISTINCTCOUNT ( Sales[ProductKey] ), "sales", COUNTROWS ( 'Sales' ) ) And got: Any help would be much appreciated. I wan to count the number of products in each year that were never sold before then, i.e. were sold in that year, for the first time ever. [1]: https://dax.do/fDTAmTl3kDIMaF/
Group by one column and get average from another column
Hello all I have following table and I want to get with DAX avg for each character, so result should be this unfornatelly GROUP BY is not working as I expect..can anyone help me to understand where I am wrong? and what should be correct formula ?DAX table to create a revenue funnel visual?
Hi! my main fact table is financial transactions which look pretty much like this: date type amount 10/12/22 revenue 100$ 10/12/22 expense 50$ 01/01/23 revenue 100$ 01/01/23 expense 30$ 05/01/23 revenue 150$ 05/01/23 expense 75$ I want to create a revenue funnel visual that shows the conversion between the revenue and the margins. from what I understand, I need to get a table at this structure ( I can drop the year but I want to keep it for future visualization) year type amount 2022 revenue 100$ 2022 margins (100$-50$) = 50$ 2023 revenue (100$+150$) = 250$ 2023 margins (100$+150$) - (30$+75$) = 145$ Then I will drag the type to the funnel group and the amount to the funnel value. How can I create this table using DAX? Thanks! Tal
Creating Separate Reports for each Group
Hello, I'm trying to create separate reports for each group (High School District). Specifically, each school district needs its own title "Quarterly Report", Payee & Payer Address, and the data table by school district. The closest I've gotten is to insert page breaks in the data table at the bottom of the report between each school district -- so in other words, I get one heading followed by page-broken tables by school district. Can someone point me in the right direction? Thank you!
