From one record to multiple records (enumerate dates)

[kazuma6666]

Hello there,

I am a beginner in Power BI.

I have a membership table (from Dynamics), and need to do reporting on it. Users will for example ask "How many members of this type do I have in 2021?".

We have multi year membership also (1-6 years). So in the table we have "date begin", "date end".

For the user to be able to ask for a specific year, as far as I know, I need to have multiple lines for each multi-year membership.

In the past I would do this in access, and create a new table. For example, a member from 2018 to 2022 will have 5 lines, one for each year. As access has more and more problems, I have to stop using it.

How can I do the same in PowerBI? I basically need to enumerate each year between "date begin" and "date end", then add a line for each year.

Example:

Source

membership1    01-01-2018          01-12-2022

Destination

membership1    2018

membership1    2019

membership1    2020

membership1    2021

membership1    2022

Thanks in advance for your time.

PS: this is the first of about a 100 queries I need to do for my membership reporting. Should I ask my management for an SQL server to do the SQL part before sending to PowerBI? From what I have seen online, some really simple things in SQL seems to be completely arcane in Power query.

Regards,

Benjamin.

[Greg_Deckler · Accepted solution]

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

[BA_Pete · Accepted solution]

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

[BA_Pete · Accepted solution]

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

[BA_Pete · Accepted solution]

@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

[BA_Pete · Accepted solution]

@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]

hello again,

I have come back to this project, but for some reason my custom column 

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

gives me an error now. This is the error:

Expression.Error: A cyclic reference was encountered during evaluation.

this is the line that gives the error:

= Table.AddColumn(#"Renamed Columns3", "year_membership", each {Date.Year(si_membershipsubscribers[creation date])..Date.Year(si_membershipsubscribers[Valid to])})

Does anyone have an idea why this doesn't work anymore?

Thanks in advance!

[kazuma6666]

I have found the reason for the cyclic error. I need to do this before renaming or reordering columns, otherwise it gives me the error.

Just in case someone else has the error.

[BA_Pete]

Kudos for keeping the thread updated with your findings to help others in the future. Good job 🙂

Pete

[kazuma6666]

@BA_Pete 

Thanks for your solution. It is now implemented (the simple version) and works perfectly 🙂

@Greg_Deckler 

I also accepted your solution, but I'm a real beginner in PowerBI. I could not test it, as I don't really know how to use it right now. I'll learn at some point.

[BA_Pete · Accepted solution]

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

[kazuma6666]

Hello again @BA_Pete ,

I'm trying to get your second solution working. I have created a date table (using this explanation: https://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query/). If I understand right, I should use these dates for my CountMembers measure. I am not sure what to use for my measure in this case.

I think what the measure needs to do is look at the input date, and output all the members who have this date between [date begin] and [date end] of their membership. Is that what's needed, or did I miss something?

Is there a specific function I can use that does that?

Thanks in advance for your time!

PS: If you have a link to an explanation/tutorial about this, that would be perfect too!

PPS: Should I create a new topic for this question?

[BA_Pete · Accepted solution]

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]

So after more testing, I can say that this works very well.

In my report, I would like to show the results for current year, last year and the year before that.

How would I go about adding the last year's data too?

I tried adding a column in my date table.

OldDateYear-1 = dateadd(PBI_Date_table[Date],-1,year)

I then created the same count measure that was described in previous post, using DateYear-1 instead of the original date.

I thought it would show me the data from the previous year, but when I used it in my report, it didn't filter based on the year I chose. It just showed all data for this specific field.

 

I also tried it in the count measure, but it only accepts column there.

CountMembersY-1 = CALCULATE( DISTINCTCOUNT(si_membershipsubscribers[si_membershipsubscriberid]), FILTER( 
si_membershipsubscribers, 
si_membershipsubscribers[Valid from].[Date] <= MAX(dateadd(PBI_Date_table[date].[Date],-1,YEAR)) &&
 si_membershipsubscribers[Valid to].[Date] >= MIN(dateadd(PBI_Date_table[date].[Date],-1,YEAR)) ) ) 

So this code throws an error and is not accepted.

Any idea what I should do to get the count of members for the previous year too?

Thanks in advance!

[BA_Pete]

Hi @kazuma6666 ,

Ok, this may get a little complicated.

If you have an active relationship between your calendar and fact table, it's as easy as this:

_noofActiveMemberships_Complete_PY =
CALCULATE(
  [_noofActiveMemberships_Complete],
  SAMEPERIODLASTYEAR(calendar[date])
)

HOWEVER, as your fact table isn't actually a transactional table (it's a Type 2 Slowly Changing Dimension table), you aren't really going to be able to create a meaningful relationship between your tables.

So, assuming that you have more work to do on this model/report, I think we might need to change our previous approach to allow simpler measure management in the future. This is where we come full-circle to Greg's concept of the 'Blowout'. I call it an 'exploded' table, and it's much simpler to do in Power Query (PQ) than Greg's DAX solution.

Create a new custom column in your fact table in PQ with the following calculation. Call it 'subsDateList', purely because this will help me to reference it later:

List.Transform(
  {
  Number.From(si_membershipsubscribers[Valid from])
  ..Number.From(si_membershipsubscribers[Valid to])
  },
  each Date.From(_)
)

You will now have a column full of nested lists. Expand this column using the button in the column header that looks like two arrows pointing away from each other. This will expand each SCD record into a new row for every date between [Valid from] and [Valid to].

At this point, you will need to decide the maximum relative time that you are going to report into the past. Firstly, so that you can immediately reduce the large number of rows you now have and, secondly, to prevent the table growing indefinitely until it's a homologous data blob that takes over the world.

By the sounds of your YoYoY requirement, you will only want to keep data from January 1st, 2 years ago. Do this by filtering on [subsDateList] as required.

Once you've got this table, you can relate calendar[date] to factTable[subsDateList], and Power BI will aggregate your measures automatically in your visuals.

Don't be too scared if there's hundreds of thousands, or even low-millions of rows in this is table. I've created entire models based on many exploded SCD tables and, while they take up more HDD and take longer to refresh, once they're loaded the reports perform remarkably well (just avoid using iterator functions over them if you can!).

So, your new measures will be:

// New number of members within period
_noofMembers = DISTINCTCOUNT(si_membershipsubscribers[si_membershipsubscriberid])

// New number of members prior year to selected period
_noofMembersPY =
CALCULATE(
  [_noofMembers],
  SAMEPERIODLASTYEAR(calendar[date])
)

Sorry it goes on a bit, hope it makes sense 🙂

PS: as an aside, I would personally recommend shortening your table and column names and making them clearer to read. For example, I would change 'si_membershipsubscribers' to 'factMemSubs', and [si_membershipsubscriberid] to [memSubID]. This will make it easier to write and manage M/DAX code going forward.

Pete

[kazuma6666]

@BA_Pete I tried implementing this new field this morning but I'm getting errors. I went into powerquery editor and added a new column in the membershipsubscriber table. In there, I added the list.transform function. One thing I just found out is that my validfrom and validto are not just dates, but date/time/timezone. Would this be a problem?

First I tried after renaming and reordering my columns, and I had the cyclic ref error. I then duplicated my table and tried to do it as the first step. At that point I'm getting this error:

Expression.Error: The column 'si_validfrom' of the table wasn't found.
Details:
    si_validfrom

Si_validfrom is the original name of the field. The strange thing is, I am using autocomplete to add it.

This is what my custom column looks like:

List.Transform(
  {
  Number.From(si_membershipsubscribers[si_validfrom])
  ..Number.From(si_membershipsubscribers[si_validto])
  },
  each Date.From(_)
)

Did I misunderstand some part of your explanation? I am not sure what's wrong.

Could this be linked to the fact that my date are in european format (day/month/year)?

Thanks in advance!

[BA_Pete]

Hi @kazuma6666 ,

Try changing your custom column to this:

List.Transform(
  {
  Number.From(Date.From(si_membershipsubscribers[si_validfrom]))
  ..Number.From(Date.From(si_membershipsubscribers[si_validto]))
  },
  each Date.From(_)
)

This should avoid the Date/Time/Timezone problem.

Pete

[kazuma6666]

Thanks for your answer. Unfortunately, I still get the same error:

Expression.Error: The column 'si_validfrom' of the table wasn't found.
Details:
si_validfrom

I am not sure what to do with this error message, as the table name and columns were filled in by PowerBI... I'm pretty sure that this is a message that has nothing to do with the actual error I have. I don't do anything else with the table before or after.

[BA_Pete]

@kazuma6666 ,

If you can see columns called [si_validfrom] and [si_validto] in your table before you add this custom column, then my guess is that the capitalisation is wrong.

M code is entirely case-sensitive, so it won't recognise [si_validfrom] if the actual column name is [SI_validfrom], or [si_Validfrom], or [si_ValidFROM] etc. You must match the case in your code EXACTLY to how it appears in the table.

This rule goes for the table name and any function/argument names too.

Pete

[kazuma6666]

Thanks for your help.

I finally understood the problem! If I use the table name, it is not recognized in the table itself.

So this:

List.Transform(
  {
  Number.From(Date.From([valid from]))
  ..Number.From(Date.From([valid to]))
  },
  each Date.From(_)
)

instead of this:

List.Transform(
  {
  Number.From(Date.From(si_membershipsubscribers[si_validfrom]))
  ..Number.From(Date.From(si_membershipsubscribers[si_validto]))
  },
  each Date.From(_)
)

I found that out when looking at a tutorial on how to create a list of dates from two dates in a table. 

Unfortunately, I get an error when updating the data after:

Load
si_membershipsubscribers
Column 'si_membershipsubscriberid' in Table 'si_membershipsubscribers' contains a duplicate value '29a780d9-2e65-eb11-a812-000d3ab1b159' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

Seems I can't multiply rows like that as I am using a database, and there are relations between tables. Any idea what I can do for that?

[BA_Pete · Accepted solution]

@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]

Hello @BA_Pete , and thanks for your answer.

I got the SubsDateList working with that last tip 🙂

I had to delete a relationship, but I'll use measures to get data from that table, it's no big deal.

I then linked my fact table (membership_subscriber) to my date table. I changed my countmembers measure to "

DISTINCTCOUNT(si_membershipsubscribers[si_membershipsubscriberid])".

It works, until I choose a year in my slicer (from the date table). At that point, nothing appears anymore. This worked with the previous version of countmembers measure. Do I need to change the way I filter things?

As a test, I made a report with the count of members and the dates from the date table, but I get blank for all dates. It seems the relation is not working correctly for some reason.

[BA_Pete]

Hi @kazuma6666 ,

Can you share a screenshot of your basic model (calendar table and our new fact table) with the relationship highlighted please?

I can only guess that this is where the issue is as Calendar-to-fact relationships should 'just work'.

Pete

[kazuma6666]

@BA_Pete Thanks! Here is a screenshot of the relation.

I am not sure why the dates don't show the same way in the fact table and the date table.

I checked, but they are dates in both cases (not date time or date/time/timezone).

Just in case it's needed, I added a screenshot of the relationship tab too.

[BA_Pete · Accepted solution]

@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]

@BA_PeteI feel stupid now, I didn't mark it as a date table... Thanks for the reminder!

Well, that's one mistake I won't make again!

I can now see values for my countMembers measure, but not for the countMembersY-1 with the following code (I included CountMembers measure):

CountMembers = DISTINCTCOUNT(si_membershipsubscribers[si_membershipsubscriberid])
CountMembersY-1 = CALCULATE(
  [CountMembers],
  SAMEPERIODLASTYEAR(PBI_Date_table[Date])
)

I am not sure why, but I can't get the year anymore in my slicer either. It doesn't show me the date hierarchy, since I maked my date table. I see I can create a date hierarchy when right clicking on the date field, but that just adds a field date hierarchy that contains the date and nothing else.