Considering Specific Days Between Two Dates

[Ryukatan10]

Hello everyone, I'm stuck with this issue and don't know how to solve it.

I have a database that show several rows like this one:

Flight	Acft	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Begin Operation	End Operation
5467	C208	0	0	0	0	0	0	7	05/01/2022	06/27/2022

In this case, I have a flight registration that will ocurr from 05/01/2022 to 06/27/2022, only at Sunday, because all the others weekday are "0" in this row.

I would like that Power BI shows or undertand something like that:

Flight	Date
5467	05/01/2022
5467	05/08/2022
5467	05/15/2022
5467	05/22/2022
5467	05/29/2022
5467	06/05/2022
5467	06/12/2022
5467	06/19/2022
5467	06/26/2022

I already have another table with dates in one column and weekday in number from 1 to 7 in other column, but even if I create a relationship with al the seven columns form the flights database with this table of dates, it not work in the way that i want.

Can Anyone Help Me?

[wdx223_Daniel]

method 1, in PQ editor, only bases on the table of Flights

NewStep= #table({"Flight","Date"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Select(List.Dates(Date.From(_{9}),Duration.Days(_{10}-_{9})+1,#duration(1,0,0,0)),(x)=>List.Contains(List.Range(_,2,7),Date.DayOfWeek(x,Day.Monday)+1)),(x,y)=>{x{0},y}))

mehod 2, in dax with the same logic

NewTable=SELECTCOLUMNS(GENERATE(Flights,FILTER(DATESBETWEEN(DateTable[Date],Flights[Begin Operation],Flights[End Operation]), VAR _wn=WEEKDAY(DateTable[Date],2) RETURN Filghts[Mon]=_wn||Filghts[Tue]=_wn||Filghts[Wed]=_wn||Filghts[Thu]=_wn||Filghts[Fri]=_wn||Filghts[Sat]=_wn||Filghts[Sun]=_wn)),"Flight",Filghts[Flight],"Date",DateTable[Date])

[tamerj1 · Accepted solution]

@Ryukatan10 
Here is a sample file with the solution https://www.dropbox.com/t/08giOHP4Wl2Oy6Q7

You need first to unpivote the weekday columns in the flights table:

Rename the "Value" column

Delete the "Attribute" column 

Filter out Zero values

Create the relationship with the Date table (Make sure the Weekday numbering system is the same in both tables). In your real data the relationship will be Many to Many

Create the table visual (Flight > from Flights table and Date > from Date table)

Create the Filter measure that will filter only the existing Dates between "Begining" and "End"

Flight Dates = 
COUNTROWS ( 
    FILTER (  
        'Date',
        'Date'[Date] >= MAX( Flights[Begin Operation] )
            && 'Date'[Date] <= MAX ( Flights[End Operation] )
    )
)

Place the measure in the Filter Pane and select "Is not blank" then apply

[daXtreme]

// All you need is a middleman table
// that you'll then join to both
// the Flights table and the one with
// dates. It'll be a helper, so it's
// best to hide it in the UI as it
// should only be used by measures.
// It's best to do such things in Power
// Query but if your data is not too
// large, then you can also do it via
// DAX. The Flights table should also
// be restructured so that the days
// are not columns but values in a column.
// The dataset you have right now is not
// in optimal format for PBI consumption.
// However, if you insist on this format,
// here's the code:
FlightDateMiddleman = // new table
selectcolumns(
    generate(
        Flights,
        var dateStart = Flights[Begin Operation]
        var dateEnd = Flights[End Operation]
        // because of the format of your table,
        // you have to do such ugly stunts...
        var dayStepSize = 
            maxx(
                {   Flights[Mon],
                    Flights[Tue],
                    Flights[Wed],
                    Flights[Thu],
                    Flights[Fri],
                    Flights[Sat],
                    Flights[Sun]
                },
                [Value]
            )
        var dates = 
            SELECTCOLUMNS(
                GENERATESERIES( dateStart, dateEnd, dayStepSize ),
                "@Date", [Value]
            )
        return
            dates
    ),
    "Flight", Flights[Flight],
    "Date", [@Date]
)

[Ryukatan10]

Hi @tamerj1  the second table is like this:

Data	Dia da Semana
01.jan.2022	2
02.jan.2022	3
03.jan.2022	4
04.jan.2022	5
05.jan.2022	6
06.jan.2022	7
07.jan.2022	1
08.jan.2022	2
09.jan.2022	3
10.jan.2022	4
11.jan.2022	5
12.jan.2022	6
13.jan.2022	7
14.jan.2022	1
15.jan.2022	2
16.jan.2022	3
17.jan.2022	4
18.jan.2022	5

I have the dates and a number that identifies the Weekday Name, 1 for Monday and 7 for Sunday

[tamerj1]

Hi @Ryukatan10 

can you share sample data if the 2nd table?