Date Slicers

[New2PowerBI]

I have many Table and columns, many of them with multiple sets of information and connected to different visualizations.

I have one visualization in particular where a customer wants to know everything that their direct reports have coming due from "TODAY" thru next year (365 days); how can I best achieve it?

For testing, I was able to add a Filter and enter the date range, but that seems to be something I'd have to redo everytime.  I know there has got to be a better way so that whenever the data is accessed it knows it's looking for data that is due that day thru 365 days. 

I read through some of the other Slicer posts but didn't quite find what I was looking for.  Any help will be greatly appreciated.

Thanks!

[parry2k]

Can you share your data mode for better answer but here is something I think will work:

You need add two columns

Current Date = TODAY()

Due in next 365 days = if(myTable[DueDate] >= TODAY() && myTable[DueDate] <= DATEADD(CurrentDate, 365, DAY), 1, 0)

And then drop this new column on filter and select where value is 1

[New2PowerBI]

I think we are close.  🙂

All values resulted in "0" but I should have both 0 and 1 based on data.  Here is what I entered into 2nd column:

Due in next 365 days = if(PM[NEXTDATE] >= TODAY() && PM[NEXTDATE] <= DATEADD(PM[Current Date], 365, DAY), 1, 0)

Do you think I need to adjust the format on my [NEXTDATE] column?  The current values' format is (example):

4/1/2017 12:00:00 AM

[parry2k]

Hello @New2PowerBI

Can you share sample data model since I don't know all the details and I proposed the situation based on some assumptions, if you can share the columns you have, it will be helpful 🙂

[New2PowerBI]

Absolutely; what portion do you want to see?  The columns, table or how the line chart is set up?

[parry2k]

columns with sample data 🙂

[New2PowerBI]

CSTM_IMPLEMENTER	NEXTDATE	PMNUM	Current Date	Due in next 365 days
JAYJ	4/1/2017 12:00:00 AM	STX68326	3/17/2017 12:00:00 AM	0
JOHND	4/2/2017 12:00:00 AM	STX68327	3/18/2017 12:00:00 AM	0
GEORGR	4/3/2017 12:00:00 AM	STX68328	3/19/2017 12:00:00 AM	0
JAYJ	4/4/2017 12:00:00 AM	STX68329	3/20/2017 12:00:00 AM	0
JOHND	4/5/2017 12:00:00 AM	STX68330	3/21/2017 12:00:00 AM	0
GEORGR	4/6/2017 12:00:00 AM	STX68331	3/22/2017 12:00:00 AM	0
JAYJ	4/7/2017 12:00:00 AM	STX68332	3/23/2017 12:00:00 AM	0
JOHND	4/8/2017 12:00:00 AM	STX68333	3/24/2017 12:00:00 AM	0
GEORGR	4/9/2017 12:00:00 AM	STX68334	3/25/2017 12:00:00 AM	0

The last two columns is what I added; but in looking at all the values, everything is 0 although I think I should have 1's.

[v-caliao-msft]

Hi @New2PowerBI,

You said that you added the last two column. How could the Current Date value is different in each row? Which fomular did you used to create this column?

CSTM_IMPLEMENTER	NEXTDATE	PMNUM	Current Date	Due in next 365 days
JAYJ	4/1/2017 12:00:00 AM	STX68326	3/17/2017 12:00:00 AM	0
JOHND	4/2/2017 12:00:00 AM	STX68327	3/18/2017 12:00:00 AM	0
GEORGR	4/3/2017 12:00:00 AM	STX68328	3/19/2017 12:00:00 AM	0
JAYJ	4/4/2017 12:00:00 AM	STX68329	3/20/2017 12:00:00 AM	0
JOHND	4/5/2017 12:00:00 AM	STX68330	3/21/2017 12:00:00 AM	0
GEORGR	4/6/2017 12:00:00 AM	STX68331	3/22/2017 12:00:00 AM	0
JAYJ	4/7/2017 12:00:00 AM	STX68332	3/23/2017 12:00:00 AM	0
JOHND	4/8/2017 12:00:00 AM	STX68333	3/24/2017 12:00:00 AM	0
GEORGR	4/9/2017 12:00:00 AM	STX68334	3/25/2017 12:00:00 AM	0

Regards,

Charlie Liao

[New2PowerBI]

That is weird; it must have copied over that way from Excel.  What I used in the New Column was:

Current Date = TODAY()

When I looked earlier it displayed as 3/30/2017 12:00:00 AM for all values. 

[New2PowerBI]

Forgot to include the other column, it contains the following:

Due in next 365 days = if(PM[NEXTDATE] >= TODAY() && PM[NEXTDATE] <= DATEADD(PM[Current Date], 365, DAY), 1, 0)