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

[BA_Pete]

@kazuma6666 ,

Ok.

1) I'm guessing the prior year issue you're describing is due to where you are expecting to see your prior year values. SAMEPERIODLASTYEAR does not apply your prior year numbers against the prior calendar table year, rather it applies them to your current year in view.

Example: Add a calendar dimension (day or year) to a table, then add your two measures to the table. You will see that your prior year measure is aligned with your current year dimension. Whenever you use SAMEPERIODLASTYEAR measures in a visual, you would always only use current year calendar dimensions on the axis.

Not sure if I 've explained this very well, but I *believe* it explains what you are describing.

2) You want to get rid of any system generated date hierarchies as they take up ridiculous amounts of HDD space on PBIX file, and your calendar table now performs this function on its own.

Go into File > Options & Settings > Options > Current File > Data Load and uncheck auto time-intel:

You can also do this at the Global > Data Load level if you want all new reports to have this setting (I strongly recommend you do this!).

Pete

[kazuma6666]

@BA_Pete Thanks for the answer 🙂

1) I am not sure I understood everything there, but I think the last year count problem might be linked to something else. I have now selected the first year of membership in my slicer and it doesn't filter my report. I have verified that the slicer is linked to the visual. 

In this figure, I can see that I have all the institutional members from 2020, 2021 and 2022. I really don't understand why that's the case from what we did before. As we have a relation between the fact table and the date table, if the date is before 2021, it should only show the membership from 2020. It looks as if the relation is just not there. As it doesn't even filter correctly, I am not surprised the last year calculation doesn't work.

I should get Power BI pro next week, this might help track down the problem as I'll be able to look at data at that point if I understood things correctly.

2) The auto date/time is already on. The global setting also. It worked before I mark the table as a date table, but I'm not sure why it stopped suddenly. At the moment I'm typing in the dates to filter the report and test.

[BA_Pete]

@kazuma6666 ,

You don't want auto date/time on, you want to UNcheck it. Your properly marked date table now supersedes this function and it is no longer required - it will only cost you more disk space and confusion.

As you have filtered the visual to before 1st Jan this year, you have removed the current year data from the visual. As I described, the prior year values created using SAMEPERIODLASTYEAR are aligned to THIS YEAR's  dates, not prior year's. Therefore, you have removed the rows on which the prior year numbers reside by applying this filter.

Example:

In the above example, I have a basic distinctcount measure, and a distinctcount sameperiodlastyear measure, exactly like yours.

The LEFT table is not filtered so you can see that prior year values are displayed against the CURRENT year calendar dimension.

In the RIGHT table, I have applied the same visual filter that you have i.e. removed any data that pertains to this calendar year (although yours is for a year earlier, but the principle is the same), and the result is the same as yours i.e. you also lose the prior year value as it resides on the CURRENT year calendar dimension row. You are effectively filtering out the current year row where the prior year data resides.

When trying out new functions/techniques, I would recommend removing all filters from visuals/pages etc. first to see what the data looks like in its raw output, then look to filter down afterwards once you are happy the output is giving you what you need.

Pete

[kazuma6666]

Thanks for the answer @BA_Pete.

I have now unchecked the auto date/time. Seems I read things a bit too fast before, sorry.

For the countMembers and CountMembersY-1 measures, I think I have another problem. 

As the basic countMembers measure is not working correctly (it is not filtered by the dates), I don't think I'll be able to make the CountMembersY-1 measure. First I need to solve the issue with CountMembers.

Right now, I can select no date or all dates, it won't affect the CountMembers measure.

I see all members, whatever I choose. I am not using SamePeriodLastYear anymore at the moment. I think I am going to start a new project and see if it works better, because there might be something wrong with the current one.

[BA_Pete]

Hi @kazuma6666 ,

If you're able to send me over a simplified PBIX (just date list fact table and calendar) with sensitive info removed, and roughly what you're trying to achieve, I'll take a proper look for you.

Pete

[kazuma6666]

@BA_Pete Thanks! I sent you a private message with a link to my simplified PBIX file.

[BA_Pete · Accepted solution]

@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

[kazuma6666]

Thanks @BA_Pete !

Well, that's embarassing... I didn't really see those ABC123, but I'm aware of them now. I thought I did the type change, but evidently I did not. I am going to assign the types for all the other columns too. I got the year filter to work too now 🙂

Your help was invaluable for this project, Thanks a lot! And sorry for the noobie mistakes...

[BA_Pete]

@kazuma6666 ,

Really happy it's worked out for you, and hopefully you can see that PBI maybe isn't as scary as it probably first seemed.

Absolutely no apologies needed, everyone starts as a noob. Hopefully I've been able to give you quite a few things to put on to your reporting checklist to avoid confusing errors going forward.

Have a great weekend 🙂👍

Pete

[kazuma6666]

Thank you for the explanation 🙂

I will implement this Monday, and let you know if I get it to work. I will certainly simplify the names also, it's a good idea.

[kazuma6666]

Thanks so much for the fast answer. This seems to work perfectly!

[kazuma6666]

@BA_Pete 

Thanks for the answer. Especially the explanations about a calendar table. I much prefer finding the correct way to do this instead of just doing the way I always did it.

I have no more time today, but will try to implement this Thursday. 

[Greg_Deckler · Accepted solution]

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