Forum Discussion
Help with measure for extracting max date
Hello!
I am tasked with extracting a max posting date from a dataset in our PowerBI dashboard.
The available date range on the slicer page is back 3 months to date. If they move the slicer to within the last week one of the card counts displays blank. This is because there is no data for the current week.
When this happens I need to display the max posting date to the card in place of 'blank'. Since all of data I need is there, adding updated sql to remove restriction on posting dates isn't an option. This dashboard is arleady a beast. I have to find a way to display the max posting date for the cust_num specificed while ignoring the slicer dates. Make sense?
Here is a sample of my dataset:
| ACCOUNT_EMPLOYEE_ID | EMPLOYEE | POSTING_DATE | CUST_NUM |
| 555111|3440000 | 100 | 2020/05/10 00:00:00 | 555111 |
| 555111|5000000 | 101 | 2020/05/10 00:00:00 | 555111 |
| 555111|5555555 | 102 | 2020/05/10 00:00:00 | 555111 |
| 555111|4444444 | 103 | 2020/05/10 00:00:00 | 555111 |
| 555111|3333333 | 104 | 2020/05/10 00:00:00 | 555111 |
| 555111|2222222 | 105 | 2020/05/10 00:00:00 | 555111 |
| 555111|0000000 | 106 | 2020/05/10 00:00:00 | 555111 |
| 555111|1111111 | 107 | 2020/05/10 00:00:00 | 555111 |
| 555111|2222221 | 108 | 2020/05/10 00:00:00 | 555111 |
| 555111|78787878 | 109 | 2020/05/10 00:00:00 | 555111 |
| 555111|98989898 | 110 | 2020/04/19 00:00:00 | 555111 |
| 555111|87878787 | 111 | 2020/04/19 00:00:00 | 555111 |
| 555111|11112255 | 112 | 2020/04/19 00:00:00 | 555111 |
| 555111|11445566 | 113 | 2020/04/19 00:00:00 | 555111 |
| 555111|11443322 | 114 | 2020/04/19 00:00:00 | 555111 |
| 555111|11009988 | 115 | 2020/04/19 00:00:00 | 555111 |
I have this which works beautifully when there is data returned:
12 Replies
- amitchandak
Super User
Anonymous , you have to replace you max date with these
calculate(MAX(ACTRANS_Turnover[POSTING_DATE]),all(Date))
or
calculate(MAX(ACTRANS_Turnover[POSTING_DATE]),all(ACTRANS_Turnover))
This is definitely a step in the right direction as I'm no longer seeing blank which is great thank you so much.
How can I specify the max date for the specified CUST_NUM in the slicer - meaning the max date displaying is 5/3/2020 but I happen to know it's 5/10/2020 becuase I'm running the SQL manually for data validation.
- parry2k
Anonymous try this
Max date = CALCULATE ( MAX ( Table[PostingDate] ), ALLEXCEPT ( Table, Table[Customer] ) )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
- parry2k
Anonymous
are you using date slicer from your data table?
- parry2k
Anonymous so you have a calendar dimension in your model that is connected to your data table and you are using date from calendar dimension? Is this a correct understanding?
