topic Re: Mixing dates from columns with dates in a slicer in DAX Commands and Tips

Mixing dates from columns with dates in a slicer

RudyL — Wed, 23 Dec 2020 15:15:06 GMT

Hi,

A dates-challenge here. I have an invoice table like the simple one at the bottom of this post. I added a slicer from a date in a calendar table to look at a certain point in time in this history. So the slicer filters the invoices.

But I also want the date value in the slicer to be a reference value for a calculation of open invoices.

For example: when i look at reference date 22-1 only 200 is unpaid, and it's in a category less than 1 month.

reference date	22-1-2020
open < 1 month	200	from invoice I1
open > 1 month	0

But when I take a reference date 2-4, there's 1000 open from recent invoices in the past month and 200 from earlier invoices.

reference date	2-4-2020
open < 1 month	1000	from invoice I4
open > 1 month	200	from invoice I2

And finally when i set the reference date to 2-7, all is well

reference date	2-7-2020
open < 1 month	0
open > 1 month	0

I managed to make the calculated columns when I used the NOW() function as a reference date. But when I try to use the reference date from a slicer in a measure like max(calendar.date) the calculated columns don't return the results I expected. I read something about not using measures in calculated columns. But I'm stuck now. Anybody wants to help me on the calculated columns and measures needed for this?

Here's the table:

Date	Invoice	Invoiced	Paid
1-1-2020	I1	1000	0
2-1-2020	I1	0	800
1-2-2020	I2	1000	0
3-2-2020	I2	0	800
1-3-2020	I3	1000	0
3-3-2020	I3	0	1000
3-3-2020	I2	0	200
1-4-2020	I4	1000	0
5-4-2020	I4	0	1000
5-4-2020	I1	0	200
1-5-2020	I5	1000	0
1-6-2020	I6	1000	0
5-6-2020	I6	0	1000

Re: Mixing dates from columns with dates in a slicer

amitchandak — Wed, 23 Dec 2020 16:17:17 GMT

RudyL , You have try like this for date slicer from Date/Calendar Table

refer example

measure =
var _max = maxx(allselected('Date'), 'Date'[Date])
return
averageX(values(Table[invoice]), datediff(max(table[Date]),_max,day))

Re: Mixing dates from columns with dates in a slicer

CNENFRNL — Thu, 24 Dec 2020 00:08:09 GMT

Hi, RudyL , you might want to try the pattern in the attached file.

Re: Mixing dates from columns with dates in a slicer

RudyL — Sun, 27 Dec 2020 15:50:40 GMT

Thanks Resident Rockstar. Santa does exist !!! 😁

Also appreciate you deliver the answer in a way that I can learn from it.

Re: Mixing dates from columns with dates in a slicer

RudyL — Sun, 27 Dec 2020 15:52:16 GMT

Thanks for this part of the solution. Very helpful.