From one record to multiple records (enumerate dates)

kazuma6666 This looks to me like Blowout! Blowout! - Microsoft Power BI Community

Hi kazuma6666 ,

The answer to your explicit question of how to split out year rows is this: create a new custom column in Power Query and add this as the calculation:

{Date.Year([startDate])..Date.Year([endDate])}

Expand the resulting list to new rows and it should duplicate rows for each year covered.

However, the answer to your tacit question is this: You don't need to split out anything into year rows. You need to create a proper calendar table, use this to slice your report for, say, year, then write measures that count the distinct number of memberID's where [startDate] <= MIN(calendar[Date]) and [endDate] >= MAX(calendar[Date]).

There's a bit more to it than this depending on exactly how you want to report, but this is the correct way to leverage Power BI's skills, not by duplicating data rows.

Pete

Hello again 🙂

For this example, you won't have a relationship between your calendar table and your fact table.

Your measures should look something like this:

This measure will count a membership as being active within your selected timeframe if it has been active AT ANY POINT within that timeframe:

_noofActiveMemberships_Partial =
CALCULATE(
  DISTINCTCOUNT(factTable[membershipID]),
  FILTER(
    factTable,
    factTable[startDate] <= MAX(calendar[date])
    && factTable[endDate] >= MIN(calendar[date])
  )
)

This measure will only count a membership as being active if it spans the ENTIRE timeframe:

_noofActiveMemberships_Complete =
CALCULATE(
  DISTINCTCOUNT(factTable[membershipID]),
  FILTER(
    factTable,
    factTable[startDate] <= MIN(calendar[date])
    && factTable[endDate] >= MAX(calendar[date])
  )
)

Use the timeframes ([Year], [Month], [Date] etc.) from your calendar table in your visuals along with these measures and it should do what you need.

Pete

kazuma6666 ,

I think you may need to review your relationships and see if you can reasonably update this table to a MANY type.

This is now essentially a fact table, so would not be expected to operate as the ONE side of any relationships.

Pete

kazuma6666 ,

Ok, the relationship itself seems fine.

Check that your calendar table spans all the dates in your fact table and vice versa.

Check that you have marked your calendar table as the date table for the model (right-click calendar in field list and go to 'Mark as date table').

Check that your calendar table has been properly created and contains a contiguous list of dates as the [Date] column.

Double-check that you are using the Calendar[Year] column correctly in your slicer.

Beyond this, I would probably need to look at the actual PBIX itself to troubleshoot as could be one of a hundred little variables causing the issue.

Pete

kazuma6666 ,

TA DA!

Your [SubsDateList] data type is wrong. You have it set as Text, but you need to change it in Power Query to Date type.

When you have finished building your queries in PQ, ALWAYS ALWAYS ALWAYS go through all of your table columns and assign them the correct data type.

For example, your calendar table is almost completely untyped columns.

Go through all of your query columns and, where you see ABC123, click on this little icon and select the correct data type before applying back to the model. It will save you (and possible me!) a billion headaches in future!

Pete