Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi I am new to Power BI and DAX, so please bear with me,
I am trying to create a MOM and WOW and YOY function and came to understand i would need a date table to do so,
i have tried many different ones and methods i found in forums but none seem to work for me,
PLease see the pictures below and see if you can figure out why
Please help me, ive at it for 3 days now trying different ways
Solved! Go to Solution.
If you order date has time the relationship won't work because PBI can't find an exact column.
Not is the Same
Order Date
01/01/2017 15:20
Calendar Date
01/01/2017
Tried changin both to date/time still no luck
Here is the table\
Thanks for you help!
Now do you know any formulas to work out month on month growth %? haha
it has time too
If you order date has time the relationship won't work because PBI can't find an exact column.
Not is the Same
Order Date
01/01/2017 15:20
Calendar Date
01/01/2017
Spliting it worked thank you!!
I always prefer to add datekey column in calendar and data table and set relation ship on that, that way, you never run into these kind of issues:
Thru power query add following column in both calendar and order table, and set relationship on this.
DateKey =DateTime.ToText([myDateField], "YYYYMMDD")
If you source field is Date Type then following use following:
DateKey = Date.ToText([myDateField], "YYYYMMDD")
Change DateKey to WholeNumber, not required thou.
Thanks,
P
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Shall i split the order date column? would that work?
yes, you can split in a easy way in Query Editor:
Select the Column, Add Column-Date-Date Only
I spoke too soon, now its putting all the revenue on each day
No my fault, the relationship broke when i split the columns, now it is working 🙂
Well, definitely something up with your relationship. Looks like your Calendar table might be off...
@Vvelarde Thoughts?
Best,
Alan
Hey @Kieran_choudary,
Your relationship looks good and I was able to recreate your calendar table. Let's test to make sure the relationship is working properly. Can you put the dates in a table and share the screenshot, like below? The first is from my "Source" table, the second date is from my "Calendar" table.
Cheers,
Alan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |