Value of latest update for each measure when last update was in a different month
Hello Community, What DAX function would you recommend to get the value corresponding to latest update of a list of KPIs where some of these KPIs are measured on monthly? Some are monthly, others quarterly and so on. Note that the latest date in Last Update column is different for each metric. I can't use a formula where the Metric is defined as a string (i.e. "A") because the metrics have long descriptions (some with more than 20 words) and my dataset has hundreds of different metrics. The only solution I found so far is "slice" the data set in multiple smaller tables and it is getting impossible manage everything. I appreciate any suggestion or ideas. KR
Calculate filter not working when adding dimension
Good morning, Firstly, wishing you a merry x-mas! 🙂 I'm working on a dashboard that it has to show different measures based on different subsets. There's a database with rows representing actions. All the rows have a column named Date that shows the date of the game. The subsets are: Last_Game = MAX(database[Date]) Other_Games = database[Date] <> MAX(database[Date]) So, to represent the data of the last game, I used: Pts/G_Last = CALCULATE([Pts/G], database[Date] = MAX(database[Date])) * With this CALCULATE, I take, from all games, the measure [Pts/G] for the subset created for the database[Date] = MAX(database[Date]) This is working when I represent the data alone but not when I add a column to see the same amount [Pts/G] separated by that column/dimension. The example below: - When using the measure alone, the expected result is correct (78). (Image 1) - When adding the dimension, the total is changing to show all games, ignoring the calculate filter. I tried also with KEEPFILTERS but not working [Pts/G_Last = CALCULATE([Pts/G], KEEPFILTERS(database[Date] = MAX(database[Date])))]. (Image 2) Could anyone tell me how I could do it, please? Thank you in advance.
Calculation revenue for the last date
Hi, I am a bit lost concerning the use of MAX with dates. I thought it would be possible to use a dynamic max date and then combine it with a calculate revenue function. Here is my issue: I need a measure that takes into account only the freshest date in my data set. So in this case, for project release it needs to return only the revenue for the 2nd of December 2020. But filtering my revenue with the max date delivers an error. I am still new at DAX and getting confused by all the possibilities. How should I approach this? Thanks! Pauline.Max function showing wrong. Need help for Powerbi Matrix total
Hello everyone, I need your support to solve and understand issues in my DAX. I have a transactionDB of all bank transaction like below: date company type Source Bank amount 01-Apr-22 Company 1 Credit Bank Bank A 10000 01-Apr-22 Company 1 Credit Bank Bank B 10000 01-Apr-22 Company 1 Debit Bank Bank B 500 01-Apr-22 Company 2 Credit Bank Bank C 10000 01-Apr-22 Company 2 Credit Bank Bank D 10000 01-Apr-22 Company 2 Debit Bank Bank C 10000 02-Apr-22 Company 1 Debit Bank Bank A 3000 02-Apr-22 Company 1 Debit Bank Bank B 1000 03-Jun-22 Company 2 Credit Bank Bank D 30000 03-Apr-22 Company 1 Credit Bank Bank A 5000 03-Apr-22 Company 2 Credit Bank Bank C 60000 03-Apr-22 Company1 Debit Bank Bank A 2000 03-Apr-22 Company 2 Debit Bank Bank C 10000 03-Apr-22 Company 2 Debit Bank Bank D I want to build a Dashboard with Matrix like below. Users wants to see today (slider date 3/06/2022) 1. Opening balance of 03/06/2022 (sum of all debit - credit as of of 2/06/2022) 2. Sum of credit happened on 03/06/2022 3. Sum of debit happened on 03/06/2022 4. Closing balance of 03/06/2022 If the user changes the date slider to 2/06/202, they will see above status as of 02/06/2022 Solution 1. Opening Measure (This is working fine with slider and all values) CALCULATE( SUM(transactionDB[Amount]), FILTER(ALLSELECTED(transactionDB[date]), ISONORAFTER(transactionDB[date],MAX(transactionDB[date])-1,DESC))) 2. Credit (Matrix is showing wrong calculation) CALCULATE( SUM(transactionDB[credit]),FILTER(ALLSELECTED(transactionDB[date]),transactionDB[date] = MAX( transactionDB[date]))) I want to show the credit happened in Slider date ie. 03/06/2022. It is not working as expected. 3. Debit(Matrix is showing for wrong calculation) CALCULATE( SUM(transactionDB[debit]),FILTER(ALLSELECTED(transactionDB[date]),transactionDB[date] = MAX( transactionDB[date]))) I want to show the debit happened in Slider date ie. 03/06/2022. It is not working as expected. 4. Ending balance (This is working fine with slider and all values) CALCULATE( SUM(transactionDB[Amount]), FILTER( ALLSELECTED(transactionDB[date]), ISONORAFTER(transactionDB[date], MAX(transactionDB[date]), DESC) ) ) Please help to solve this issue. I understand that using max function will summarize the value based on maximum date filtered within its context. But i would like to show the debit or credit happened for that particular date as per date slider ChandeepChhabra GuyInACube johnt75 tamerj1Get the max value per date based in serial
I have 3 fields Serial, Date and Temp. The date field is actually a date time field I converted to just a date. I am trying to get the max Temp per date for each serial number. In the past I have done this to get the max date of each serial and filter on only the columns with 1. Max Date = IF('Export'[datetime]=CALCULATE(MAX('Export'[datetime]),ALLEXCEPT('Export','Export'[serialnumber])),1,0) The complexity here is adding the Temp. I need to flag each row somehow that this is the max temp for that serial on that day. Here is a sample of what I am trying to do. I dont necessarly need a max temp column with 1,0 but that how I was able to do it before.Max date in a column
Hi All, I have a date column called FileDate, and I'm trying to get the latest date in that column. I've tries using MAX or LASTDATE in the following ways: maxFileDate = LASTDATE(('myTable'[FileDate].[Date]) maxFileDate = MAX('myTable'[FileDate].[Date]) maxFileDate = CALCULATE(MAX('myTable'[FileDate].[Date]), All()) All return 12/31/2021 although the last date in that column is Nov 9th What am I missing here? Thanks for the help!
