Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

1 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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
aksh
Microsoft Employee
Microsoft Employee

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
Super User
Super User

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks smoupre, it's a great solution!

Anonymous
Not applicable

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors