Kevin Arnold

This post answers a question posted on the SQLServer Community slack #powerbi channel.

I am trying to build this report where I have a start and end date for each record and chart out the values. Ideally, if the month is in between the start and end date I would grab that value and chart it like this. How do get the Desired Data Entry to format like the Desired Report data?

Setting up the source

I set up a spreadsheet with the data above, rows 2-4. I used Power Query to Unpivot the columns, resulting in a row per project and type.

I added a Date table to the model and the relationships to the Start and End Dates. You do not need these relationships for the solution, but I do this out of habit. You will notice on the Date table, a column named EOM. It contains the last day of the month for each date. I use that to help produce another table I called “Disconnected Date,” which is filtered to have one row per month.

Joining the tables

The source data, in the Sheet1 table, is joined to the Disconnected Date table using the CROSSJOIN function. I first filter the Disconnected Date table to the currently selected month value. The Cartesian product is further limited to the rows with the Disconnected Date value between the Start and End date. The resulting table is iterated by the SUMX function to aggregate the value.

Total Value in Month = 
VAR _Month =
            SELECTEDVALUE ( 'Disconnected Date'[Month Number] )
VAR _Crosstab =
    FILTER (
            CROSSJOIN (
                Sheet1,
                FILTER ( 'Disconnected Date', 'Disconnected Date'[Month Number] = _Month )
            ),
            AND (
                'Disconnected Date'[Date] >= Sheet1[StartDate],
                'Disconnected Date'[Date] <= Sheet1[EndDate]
            )
        )
RETURN
    SUMX ( _Crosstab, 'Sheet1'[Value] )

Do you know of other way to solve this? Let me know.