CREATE A TABLE VISUALIZATION WITH A RANGE OF DATES

[Anonymous]

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

[aksh]

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?

[Greg_Deckler]

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.

[Anonymous]

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!

[Greg_Deckler · Accepted solution]

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.

[Anonymous]

Thanks smoupre, it's a great solution!

[Anonymous]

Filtering on a dynamic date range is possible but the best solution will probably depend on your data source and model design.

For example we're using SSAS Multidimensional and have added a set of flags to our date dimension to show current month, year etc. We then just have to use these flags in a filter and the date range moves every time the report is refreshed.

You could do similar things in an SQL source or by adding a calculated column into your model after the data is loaded.