Forum Discussion
CREATE A TABLE VISUALIZATION WITH A RANGE OF DATES
Hi to everyone,
I have a table of dates (referring to transactions) and I want to create a range of dates between two specified dates.
I want to visualize the result on a table visualization, how can I do it?
I already tried with these 3 functions:
(some examples)
DATESBETWEEN -> DATESBETWEEN(Dates[DataUltimaTxn];DATE(2009;12;09);DATE(2014;01;08))
DATESINPERIOD -> DATESINPERIOD(Dates[DataUltimaTxn];DATE(2014;01;08);-30;DAY)
CALENDAR -> CALENDAR(DATE(2009;12;09);DATE(2014;01;08))
but all of them give me the same error when I try to visualize the result on a table. The error is:
"A table of multiple values was supplied where a single value was expected."
Can you help me? How can I visualize multiple dates on a table?
Thanks
So, not necessarily a great solution, but you could do something like create a new column with a formula such as:
Date Range = IF(TODAY()-[Date] = 0,"Today",IF(TODAY()-[Date] < 30,"< 30 Days",IF(TODAY()-[Date] < 60,"30 - 60 Days","> 60 Days")))
Then, create a slicer based on Date Range. You click the slicer, it filters the visualizations on the page to those date ranges.
Obviously, this does not give the user the ability to enter specific dates and such.
I know that parameters or input fields have been a fairly reoccuring feature request.
6 Replies
Perhaps I am not understanding what you are trying to do, but what about just using visualization/report/page filters?
Table
Date Column1 Column2
1/1/2016 xx yy
1/2/2016 xx yy
1/3/2016 xx yy
You would set your filter for values in Date after 12/31/2015 AND before 1/4/2016 for example.
Hi smoupre,
you're right, and thank you for the solution. But I expressed myself badly.
Your solution is perfect if I insert manually the values, but if want to use two calculated value like function TODAY() or TODAY()-30 as values? How can I solve it?
. . .
Meanwhile, do you know something about the implementation of "input fields" in Power BI? They would be perfect for me as a solution.
Still thank you so much for your fast answer!
So, not necessarily a great solution, but you could do something like create a new column with a formula such as:
Date Range = IF(TODAY()-[Date] = 0,"Today",IF(TODAY()-[Date] < 30,"< 30 Days",IF(TODAY()-[Date] < 60,"30 - 60 Days","> 60 Days")))
Then, create a slicer based on Date Range. You click the slicer, it filters the visualizations on the page to those date ranges.
Obviously, this does not give the user the ability to enter specific dates and such.
I know that parameters or input fields have been a fairly reoccuring feature request.
When I use the below formula I get an error :"A table of multiple values was supplied where a single value was expected"
DatesinPeriod(Table[Invoice Date],Max(Table[Invoice Date],-3,Month)
whereas If I use the above formula with NumberofIntervals paratmeter as 3 i.e DATESINPERIOD(Table[Invoice Date],MAX([Invoice Date],3,MONTH) , it does not throw any error
What is the problem?
