Measure to calculate the max date from the slicer and us it in another measure

[mahkooh]

I have a column called 'DateInvoiced' from a table called 'Debtors'.

In the report I have a slicer to select the DateInvoiced between two dates. 

Also in the report, i have some logic which stops some rows being shown.

The slicer is able to select every date between the min and the max in the column DateInvoiced, even when a date does no have a record against it (e.g. The last record in December was 24th of Dec, but the slicer allows you to select the 31st Dec as the top date despite there being no record on the 31st December.

Using CALCULATE on the MAX of ALLSELECTED, i get the max date in the table between the max and min dates selected in the slicer.

What I want is the max date selected on the slicer so i can calculate a DateDiff between the date invoiced and the max date on the slicer.

Any ideas?

[Fowmy · Accepted solution]

@mahkooh 

I would like to suggest you use Dates table and create a relationship to your Debtors table. Once you assign the Dates date cololumn on the slicer, you will be able to capture max of the selected date. 

[DataNinja777]

Hi @mahkooh ,

Based on the structure of the data, it is reasonable to assume that the Debtors table represents an open item list from Accounts Receivable (AR), where only outstanding invoices remain in the dataset. This means that once an invoice is fully cleared after cash collection, it would no longer appear in the table. The DateInvoiced field likely represents the original invoice date, and the amounts are expected to reflect only open balances, rather than historical transactions.

If this assumption holds, any measures created based on this data, such as calculating MaxDateSelected, would be working with a filtered dataset that excludes invoices that have already been settled. The slicer applied to DateInvoiced would only filter open invoices, ensuring that the MaxDateSelected measure is based on the latest available invoice date from the remaining outstanding amounts.

MaxDateSelected =
MAXX(ALLSELECTED(Debtors), Debtors[DateInvoiced])

To further validate this assumption, a check could be performed by summing the outstanding amounts in the Debtors table and comparing them against the AR ledger balance. This would confirm whether the dataset accurately reflects only open items. If needed, a measure can be created to sum the outstanding balances:

TotalOpenAR =
SUM(Debtors[Amount])

This would allow for a reconciliation with the general ledger to ensure that the data aligns with the actual AR open items.

Best regards,

[mahkooh]

Thank you DataNinja777. I actually wnat ot be able to report Debtors in any particular time period so the table is a reporting table, not something that can be compared to the AR ledger unless I am also keep an AR Ledger history table (i believe).

Thank you for taking the time to repsond. I think i will have to go for the date table option.