Forum Discussion
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
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
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
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
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 "
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
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...
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
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
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
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
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
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_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.
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.
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 Thanks! I sent you a private message with a link to my simplified PBIX file.
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