Forum Discussion

Ryukatan10's avatar
Ryukatan10
Regular Visitor
4 years ago
Solved

Considering Specific Days Between Two Dates

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 Ope...
Date
Weekday calculation
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
daXtreme
Icon for Solution Sage rankSolution Sage
4 years ago
// 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]
)