Help with measure for extracting max date

[Kgathright]

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: 

Max_Posting_Date = if(([EmployeeCount_Measure1]=0 || ISBLANK([EmployeeCount_Measure1])=TRUE),MAX(ACTRANS_Turnover[POSTING_DATE]),0)

[amitchandak]

@Kgathright , 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))

[Kgathright]

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]

@Kgathright 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!

[Kgathright]

This is what I ended up using: 

Max_Posting_Date = calculate(MAX(ACTRANS_Turnover[POSTING_DATE]),all('Accounting Calendar'[FullDate]))

[Kgathright]

I found a defect with this logic due to a unit test scenario: 

I have a Report Date Range and an Available Date Range on the slicer page. End user wants to check February numbers, my max posting date is 5/20 as it is the max date available in the dataset, so it is doing exactly what I asked since the dataset incorporates the Available Date Range which is three months back. 

How do I get the max date that falls within the Report Date Range? 

[Kgathright]

Nevermind! Once I stepped away and came back the solution was obvious to me! 

Max_Posting_Date = CALCULATE(MAX(ACTRANS_Turnover[POSTING_DATE]),ALL('Accounting Calendar'[ReportEndDate]))

[parry2k]

@Kgathright 

are you using date slicer from your data table?

[Kgathright]

The data slicer is using Accounting Calendar table, to which I have access 

[parry2k]

@Kgathright 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?

[Kgathright]

The dataset I'm supposed to get this max date from isn't connected to the Accounting Calendar table. The posting_date date ranges are hardcoded in the SQL. 

[parry2k]

@Kgathright there are lot of moving parts, you need to share 

- how you are tables are connected

- what you are using for slicers

- where you want the max date from

[Kgathright]

slicers are using the Accounting Calendar table

dataset in which the max date should come from isn't connected to the Accounting Calendar table it is connected to another table using the ACCOUNT/EMPLOYEEID