topic Re: Calculate Values to a selected Date in DAX Commands and Tips

Calculate Values to a selected Date

JB_AT — Thu, 17 Oct 2024 12:53:37 GMT

I hope you can help

I have the requirement that when a date is selected in the Date slicer, that I calculate the Invoice data up to and including that date.

The below measure partially works, but when added to a visual, it shows data after the selected date in the slicer.

The ValidFrom and ValidTo are there to show changes within the Invoice, so the invoice could have mulitiple rows. I want the selected date in the slicer to filter the table where is falls into the filters I set below. Basically taking a snapshot of the data the way it was on the selected date.

On top of that I want to calculate all open invoices up to the selected date in the slicer.

Please note I am using a between date slicer, set to After, so the StartDate is only changable,

Any suggestions on how I can prevent the result appearing after the selected date in the slicer?

Thanking you in adavnce

Customer Invoice - Open Amount To Selected Date = VAR _SelectedDate = MAX ( 'DIM Date'[Date] ) RETURN CALCULATE ( SUM ( 'CustomerInvoices'[Open Amount] ), 'CustomerInvoices'[DueDate] <= _SelectedDate, 'CustomerInvoices'[ValidFrom] <= _SelectedDate, 'CustomerInvoices'[ValidUntil] > _SelectedDate, REMOVEFILTERS( 'DIM Date'[Date] ) )

Re: Calculate Values to a selected Date

DataNinja777 — Thu, 17 Oct 2024 13:45:38 GMT

Hi JB_AT ,

Here’s an adjusted version of your measure:

Customer Invoice - Open Amount To Selected Date = VAR _SelectedDate = MAX('DIM Date'[Date]) RETURN CALCULATE( SUM('CustomerInvoices'[Open Amount]), 'CustomerInvoices'[DueDate] <= _SelectedDate, 'CustomerInvoices'[ValidFrom] <= _SelectedDate, OR( 'CustomerInvoices'[ValidUntil] >= _SelectedDate, ISBLANK('CustomerInvoices'[ValidUntil]) ), REMOVEFILTERS('DIM Date') )

I added an OR condition to account for cases where the ValidUntil date might be blank (indicating the invoice is still valid).

Using REMOVEFILTERS('DIM Date') ensures that the measure operates correctly within the context of the slicer without inheriting conflicting filters from the visual.

The measure now takes a "snapshot" by ensuring that only invoices valid as of the selected date are included.

This should prevent the result from showing data beyond the selected date while correctly aggregating open invoices up to and including that date. Let me know if this version resolves your issue!

Best regards

Re: Calculate Values to a selected Date

JB_AT — Fri, 18 Oct 2024 05:06:45 GMT

DataNinja777

Unfortunately the measure still returns values after the selected date. I forgot to mention that I have an active relationship between the Date and MaxDueDate columns

Thanks

Re: Calculate Values to a selected Date

Anonymous — Mon, 21 Oct 2024 09:43:10 GMT

Hi JB_AT ,

Based on the description, try deleting a relationship between two date columns and removing the REMOVEFILTERS function.

If a relationship exists, the date selected by the slicer directly affects the date column of the other table.

Besides, can you provide the sample data? What is the ValidUntil column?

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.