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éphane
LAST DATE WITH SALE BY CUSTOMER
Hi! I have a situation, i hope anybody could help me. I have 2 tables, table A kardex customers. And table B sales by date and customer's. tableA. Table B. I need a measure or calculated column in table A, that showme the last date of sales of that customer, like green column: thank you.
LASTDATE Not working as required
Hi guys, I am trying to retrieve the most recent QTY On Hand for each product in my inventory but the trouble I am having is that for whatever reason, some of these products can be present on multiple order lines of the same order with the same order date and time making it difficult to calculate the most recent QTY On Hand. Below, is an example of such case. My formula is the following : LAST INVENTORY VALUE = VAR X = CALCULATE(MAX(INTRAN00[TXQOH]),LASTNONBLANK(INTRAN00[TRANSACTION DATE],1)) RETURN X Highlighted in Green is the value I want to retrieve but my formula currently retreives 298. But , Based on the time reference (TXTIME) the most recent transaction is 7394480 *TXORD#) focusing on the last line (TXLINE) of that order which is 4. So essentially my formula needs to take into account order time as well as the very last line number in instances such as these. Any recomendations ?End of Month in Columns
Hello Team, I want to display the values of the last day of every month in columns. Is there a way to do that ? So basically, I want a table With name of the parameters on the left most column and corresponding values of last day of every month in the year. Parameter 12/31/2020 1/31/2021 2/28/2021 3/31/2021 ... ... I have tried LAST DATE and ENDOFMONTH but somehow they dont seem to workDirect Query Calculate LastDate issue
I am creating a visual for a Direct Query. I need to return the LastDate from my Date Dimension table (Rolling Calendar) and pass that into my CALCULATE Function to sum the TotalDrawers for that date. I keep getting a locking error. When I check it in DAXStudio it indicated there is an issue with LASTDATE. Seems that this has to do with the LASTDATE being used in a CALCULATE function on a Direct Query. Any thoughts on how to resolve the issue? WTDDrawers = VAR SelectDate = LASTDATE( 'Rolling Calendar'[Calendar Date] ) VAR DayNumberOfWeek = WEEKDAY ( LASTDATE ( 'Rolling Calendar'[Calendar Date] ), 1 ) //VAR TestFirstDate = DATEADD ( SelectDate, ( -1 * DayNumberOfWeek ) + 1, DAY ) RETURN CALCULATE ( SUM ( _AE1WeekComp[TotalDrawers] ), DATESBETWEEN ( 'Rolling Calendar'[Calendar Date], DATEADD ( SelectDate, ( -1 * DayNumberOfWeek ), DAY ), SelectDate ) )
