Forum Discussion
Post PatronRunning total per customer
Post PatronMy PBIX (anonymized) is here: https://ufile.io/0btas
What you see is that when somebody isn't in the dataset anymore in a specific month he does still count in the next month(s). That's not as intended to be.
See client 1531; he is gone in after 201803. From there on I except 0 in april, but it does count 456 for every month. So it is doing what a normal RT needs to do, but I want it to go to 0 from april.
Community Champion
I think you should modify the X Axis.
Create a new year-month column (on fact_per_month table) which comes from datum and not date and then add it to the chart
Column = FORMAT(fact_per_month[datum], "MMM YYYY")
- MiKeZZa
This seems to work. Why is this?
I can imagine that the datedim goes untill 201803 because of the longest during customer and now it's record per record so its 201803 for 1531 and 201712 for 1533. But what about things as 'make a good model with a starschema' and so on?
How can I tell my powerusers who Analyze in Excel that they don't have to use the datedim here but the datefields from the fact (which is normally hidden because of that I make the measures in a separate table)?
v-cherch-msftMicrosoft Employee
- themistoklis
Basically you need to understand how PowerBi deals with this kind of relationships between the tables.
Date table is just a superset (manually specified date ranges) but the actual data is on fact_per_month field which is the one that will give you the information that you want.
Date table normally gives you the option to retrieve date and time related data. These data could have also be taken from fact_per_month table without using the Date table at all.
I hope it makes sense.
- MiKeZZa
Yes this make sense. At all! This was also what I ment to say. I think I understand the model good enough to understand that the datedim gets all the values that are available in the fact, when you take a date month in the fact t's based on per customer. So that's 100% clear.
But this is a rupture to the statement that PowerBI models must be based on a traditional starschema by favor. And what about special things where a date dim is ment for (lets say; last 13 months, is current year, is current accounting year, quarter and so on).... These kind of reportquestions make a date dim useless I think. That's what surprires me very; the concept is clear to me.