Hello.
I am building a model with targets and results from different domains and in the most of the cases, my data doesn’t have a date column, it has a month and year columns. My question here is how should I create my date table then? Only with month Year? I need the date for one of the data sets as well. Should I maybe convert the month year to a date format?
Thanks in advance!
Best regards,
Aracelli
Definitely attempt to work an actual date into that table and then link it back to a date table.
There are a few way you can work a date into the table, using potentially RELATED or LOOKUP table, but would have to see more around the actual tables you have your model and what they look like, specifically the month and year columns.
If you want to add your model here you can and it would assist it getting this solved sooner.
Certainly give this a go first and get the date table into your model and see if you can work a way to get a date column into the appropriate place in your fact table.
Chrs
Thanks Sam, my model looks like the picture. This is just my first draft. I still need to work on it but I wanted to try already the if the relationship with calendar table would work with month/year and quarter/year. As you can see, I have created relationships for this field with a many to many relationshio single direction (date filters specific table). Is this correct? If I do this, it´s ok to not have a date form for the month-Year and for the quarter-Year?
With the model i have built so far, i am facing a small issue. I cant place in a visual the Dealer_Id and Bonus-Id together because it gives me an error (print screens below), this is for the table “Selekt proposed” this table contains the group_id instead of the dealer_Id. A group can have 1 or more dealers. It’s why there is relationship between the dealers table and dealers group table but it’s not working. I also create a crossjoin table between the dealers table and bonus table since every bonus is applicable to every dealer but there is no direct relationship between them or common fields. This looks ok for you or i am missing something? I have different tables because they come from different sources.
Thanks in advance!!!
Cool, like the setup.
There’s a bit to the model no doubt but I can understand it pretty quickly by the way you’ve laid it out.
You date table though should have no relationships one to many.
They should all be one to many.
I want you to check out this particular video.
As I have realized it very similar. You have a month and year forecast and you need to compare it to your actuals.
Check out all the techniques used in this around the model and the calculations.
What I’ve done in this example is take things even further than I mentioned earlier. I jump into the TREATAS function. I think this is perfect for what you need and think you’re up for using it.
Check out how I worked this in.
If you have time to watch the full workshop check it out here. Plenty of techniques covered in this one.
See how you go with this and let me know.
Thanks Sam, I will check it now but i get confused here. My date table should have no relationships one to many? They should all be one to many? Is this correct? So many to many is not an option, right?
Thanks and regards,
Aracelli
Sorry my apologies, was writing too fast.
No Many to Many.
Should be One to Many in nearly all cases.
One thing I’ve been playing around with a lot is TREATAS. This allows you to create cleaner models when dealing with budgeting and forecasting information at different granularities.
Definitely check out the material on this, I think you’ll agree with me when you learn how to use them.
Here’s another video on it