Filter between range dates

[powerbirino3]

Hi folks,

I know what I need to do but I don't exactly how to do it or if it's possible to do it with Power BI Desktop.

Let's say I have a table like this:

Projects table:

Name    Beginning date    Finish date
COM1    01/01/2015       31/12/2016
COM2    01/02/2016       30/11/2018

Our customer have requested us, that if they filter by 2016, projects where 2016 is "included" should appear in the matrix visual, not only projects where "Beginning date" or "Finish date" is 2016. Then, the solution I thought is to create another calculated table which would have this format:

Table of projects with years:

Name    Year
COM1    2015
COM1    2016
COM2    2016
COM2    2017
COM2    2018

So, filtering by 2016, both COM1 and COM2 projects would appear in the matrix visual, but I don't know exactly how to do this.  I've investigated searching in the forum without finding any solution and also thought about using "Group by" DAX function or other functions but I don't achieve it. Maybe unique solution is by doing a direct query to SQL database and formatting the result with that format.

Thank you guys

[MarcelBeug · Accepted solution]

In Power Query (via Edit Queries) it can easily be done by adding a custom colum with lists with years from beginning date through year of Finish date, remove the date columns and expand the column with nested lists.

Code:

let
    Source = Projects,
    #"Added Custom" = Table.AddColumn(Source, "Year", each {Date.Year([Beginning date])..Date.Year([Finish date])}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Beginning date", "Finish date"}),
    #"Expanded Year" = Table.ExpandListColumn(#"Removed Columns", "Year")
in
    #"Expanded Year"

Alternatively, you can create a table with individual dates. I think that  would give more flexibility with DAX and reporting.

Adjust the data type of the dates to whole numbers, then add a column with nested lists, remove columns, expand the nested lists and adjust the data type to date.

let
    Source = Projects,
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Beginning date", Int64.Type}, {"Finish date", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each {[Beginning date]..[Finish date]}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Beginning date", "Finish date"}),
    #"Expanded Date" = Table.ExpandListColumn(#"Removed Columns", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}})
in
    #"Changed Type1"

[JasonG-BI]

@MarcelBeug

Can this be done in direct query?

I have a similar problem that i am stuggling with 

>http://community.powerbi.com/t5/Desktop/Slicer-to-show-dates-between-on-stock-report/m-p/394930#M180...

Any help is much appreciated.

[powerbirino3]

Simply awesome. Didn't know this.

Thank you!