Sum only Values where another Column has Data
Harder than it looks: I have this simple table like below: I want to calculate how we're performing based on the goal in a Card. So I need to Sum only the goal values where the Gross Adds column is not blank(GAs are in the past and Goals are for the whole month). In theory this should work, but it puts a total for all the months previous rather than what is selected in the slicer. What's strange is if I replace [GAGoal] with [Gross Adds] in the measure below it correctly adds up the Gross Adds for the current month selected in the slicer. GoalTotalCurrent = VAR LastDaySelection = LASTNONBLANK ( 'Calendar'[Calendar Date], [Gross Adds] ) VAR CurrentRange = DATESBETWEEN ( 'Calendar'[Calendar Date], MIN ('Calendar'[Calendar Date] ), LastDaySelection ) RETURN IF ( LastDaySelection >= MIN ( 'Calendar'[Calendar Date]),CALCULATE ( [GAGoal], CurrentRange )) I've tried things like this, but it results in blank: SumGoals = IF(SUMX('Subscriber Activity',[Gross Adds])<>Blank(),SUMX('Subscriber Activity',[GAGoal])) So it shouldn't sum 1/8,1/9,or1/10 Date Goal Gross Adds 1/1/2025 5 6 1/2/2025 7 5 1/3/2025 8 7 1/4/2025 7 7 1/5/2025 4 3 1/6/2025 3 2 1/7/2025 2 1 1/8/2025 5 1/9/2025 5 1/10/2025 5
count rows on condition only for last date
Hi, I'm beginner in DAX. I've got a dataset like product, date, completed (a boolean value True/false), amount. I need to get the count of not completed rows where completed is false only for the last date by products. product, date, completed product1, 2023-12-01, false product2, 2023-12-01, false product3, 2023-12-01, true product4, 2023-12-01, false product3, 2023-12-02, true product3, 2023-12-03, true product2, 2023-12-03, false product4, 2023-12-04, true product3, 2023-12-04, false product3, 2023-12-05, false At 2023-12-05 (or after if no more data are added) I have product1, 2023-12-01, false product2, 2023-12-03, false product3, 2023-12-05, false product4, 2023-12-04, true My goal is to have a count of the completed_0 and completed_1. something like : count_completed = 1 and count_not_completed = 3. And when visualising by product, I like to have product1 : 1 not completed, 0 completed product2 : 1 not completed, 0 completed product3: 1 not completed, 0 completed product4 : 0 not completed, 1 completed When changing the global filter on date to go back in time, the "last date" should change the results according to the selection. I tried many approach using CALCULATE, LASTNONBLANKVALUE ... without result. Is a DAX approach is possible to answer this question ? what measure should I use to get the result I need ? Thanks for your help. StéphaneGet earliest date by condition
Jolly Day, I am needing to create a measure to get the earliest date by Item Code. I am needing only one result for item code filtered by the earliest date. I tried using the filter on the visual to get the earliest date but my results go wonky so I am needing to do this in a measure. What I am currently getting. What I am needing Item Code WO_DueDate 00-200463 12/16/2022 Many thanks in advance!! 🙂
TOP N with condition on another measure
Hello Guys, I need your help, because I a have a fact table like this : I need to identify the product with the best average "Note", but only with products sold over to 50 units (sum of quantity). With the summarize/addcolumns, it's easy to retrieve for each product the both information : "average note" & "total quantity": As, you can see, the TOP1 Product, with the best average note AND with more of 50 units sold is Product C. Please, can you help me to find the correct formula to apply the top 1 on the "Summarize table" but with the filter on the "Total Quantity" (or another method...) ? For information, I have a fact table with a lot of dimension (date, product, region, Supplier etc...) and the formula need to work with all slicer in the report. Thanks in advance.
