Dynamic Date

[js2246]

I have a case where I have mulitple dates that define unique variables and I want to be able to customize a date range where people can get totals based on the date range selected.

So for instance I might have Dates that look like this:

Date1	Date2	Date3	Date4	Date5	Date6
24-Dec-16	31-Jan-17	31-Jan-17	.	.	.
8-Feb-17	8-Feb-17	8-Feb-17	8-Feb-17	15-Feb-17	13-Feb-17
15-Apr-17	13-Apr-17	13-Apr-17	13-Apr-17	13-Apr-17	13-Apr-17
8-Feb-17	12-Feb-17	8-Feb-17	12-Feb-17	8-Feb-17	9-Feb-17
5-Apr-17	5-Apr-17	5-Apr-17	10-Apr-17	5-Apr-17	5-Apr-17
21-Apr-17	21-Apr-17	23-Apr-17	.	21-Apr-17	29-May-17
25-Mar-17	20-Mar-17	25-Mar-17	03-Apr-17	18-Feb-17	22-Mar-17
31-Jan-17	28-Jan-17	31-Jan-17	31-Jan-17	31-Jan-17	31-Jan-17
8-Apr-17	9-Apr-17	8-Apr-17	10-Apr-17	8-Apr-17	15-Apr-17
26-Jan-17	31-Jan-17	31-Jan-17	02-Mar-17	31-Apr-17	03-Feb-17

So I might have the variables:

var1=sum(if (Date1< 31-Jan-17 , 1, 0))

var2=sum(if (Date2< 31-Jan-17 , 1, 0))

var3=sum(if (Date3< 31-Jan-17 , 1, 0))

etc

but I want to be able to change the 31-Jan-17 dynamically based on a date selected how would be the best way to do this?

[parry2k]

you need a date dimension table in your model, from that table use date as a slicer and in your formula replace "31-Jan-2017" with MAX(DateTable[Date])

[js2246]

Thank you for your response but could you provide more details please. I'm newer to this.

At what point do I do this? 

If I create a date table based on entered data then try to create new columns for my calculated variables based on that datetable[date] then I would have to establish some sort of relationship between the date table and my original table correct? And I can't establish a relationship between the datatable[date} and all of my dates, correct?

I tried no relationship and created new column  var1 = if (sheet1[Date1]<max(datetable[date]),1,0) 

and for daterange[date] i filtered to one date value but my var1 result is not effected.

[parry2k]

no you don't need to establish relation, it will work without relation, you just want to get date from the slicer.

https://www.mssqltips.com/sqlservertip/4857/creating-a-date-dimension-table-in-power-bi/

http://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

[js2246]

I don't know what I'm doing wrong then because I can't get the slicer to change anything.  Var1 is set to the sum, and clicking on different dates doesn't effect it at all.  Do you see something I'm doing wrong?

[parry2k]

Seems like you are adding var1 as column whereas it need to be Measure since you are using sum aggregation.

[js2246]

I'm not sure how to write this as a measure. 

I get the error a single value for 'Date1' in table 'Sheet1 cannot be determined. This can happen when a measure formula refers to a column that contains many values whithout specifying an aggregation.... 

I wouldn't think aggregating the dates would work since the vars are defined independently from them

[parry2k]

i guess it need bit more tweaking, can you share your data in excel sheet and get back to you with solution. cheers!

[js2246]

I was just using the example data and variable definitions shown in the original post. I can't share my actual data of millions of records but I figure if we could get this short example problem working then I could apply the solution to my actual data. Thanks for your help!

[parry2k]

no problem, will work on it tomorrow.