Forum Discussion
Dynamic Date
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?
9 Replies
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])
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.
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
