Handling Multiple Dates in Power BI With DAX

Подписаться 92 тыс.

Просмотров 91 тыс.

50% 1

Watch the 2022 Update of this video here: • Handling Multiple Date...
Learn how to work with multiple dates within an underlying fact table by using the USERELATIONSHIP function within CALCULATE
**** Video Details ****
0:13 - introduction
1:50 - natural filtering with the active relationship
3:38 - using USERELATIONSHIP function
5:10 - what confuses users when working with multiple dates
5:48 - conclusion
**** Learning Power BI? ****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ulti...
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ulti...
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-...
FREE - Power BI Resources - enterprisedna.co/power-bi-reso...
Enterprise DNA Membership - enterprisedna.co/membership
Enterprise DNA Online - portal.enterprisedna.co/
Enterprise DNA Events - enterprisedna.co/enterprise-d...
**** Related Links ****
Show Results Up To Current Date Or A Specific Date In Power BI - blog.enterprisedna.co/2019/07...
Show Days Before Or After A Selected Date Using Power BI - blog.enterprisedna.co/2019/07...
The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI - blog.enterprisedna.co/2018/12...
**** Related Course Modules ****
Time Intelligence Calculations - portal.enterprisedna.co/p/time...
Power BI Super Users Workshop - portal.enterprisedna.co/p/powe...
Advanced Data Transformations & Modeling - portal.enterprisedna.co/p/adva...
**** Related Support Forum Posts ****
Managing common metrics across multiple date tables - forum.enterprisedna.co/t/mana...
‘Role-Playing’ Multiple Date Dimensions to Index New Store Openings - forum.enterprisedna.co/t/role...
Want Totals For Multiple Dates not connected to Dates Table - forum.enterprisedna.co/t/want...
For more multiple dates support queries to review see here -
forum.enterprisedna.co/search...

Опубликовано:

2 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 76

@EnterpriseDNA 4 года назад

***** Related Links ***** Show Results Up To Current Date Or A Specific Date In Power BI - blog.enterprisedna.co/2019/07/24/show-results-up-to-current-date-or-a-specific-date-in-power-bi/ Show Days Before Or After A Selected Date Using Power BI - blog.enterprisedna.co/2019/07/14/show-days-before-or-after-a-selected-date-using-power-bi/ The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI - blog.enterprisedna.co/2018/12/03/the-dateadd-function-the-best-and-most-versatile-time-intelligence-function-in-power-bi/ ***** Related Course Modules ***** Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-calculations Power BI Super Users Workshop - portal.enterprisedna.co/p/powerbisuperuserworkshop Advanced Data Transformations & Modeling - portal.enterprisedna.co/p/advanced-data-transformation-and-modeling ***** Related Support Forum Posts ***** Managing common metrics across multiple date tables - forum.enterprisedna.co/t/managing-common-metrics-across-multiple-date-tables/734 ‘Role-Playing’ Multiple Date Dimensions to Index New Store Openings - forum.enterprisedna.co/t/role-playing-multiple-date-dimensions-to-index-new-store-openings/2116 Want Totals For Multiple Dates not connected to Dates Table - forum.enterprisedna.co/t/want-totals-for-multiple-dates-not-connected-to-dates-table/1328 For more multiple dates support queries to review see here - forum.enterprisedna.co/search?q=multiple%20dates

@FlareSoul69 4 года назад

Simply beautiful! Hands down concise and interesting to follow video!

@patrow2 5 лет назад

This really, really, really helped me! Thanks a lot! You've got yourself a new subscriber! Thanks again!! :D

@Sky_Hi_View_ZA 9 дней назад

This has helped me solve my issue! Thank you!

@iHickUp 6 лет назад

Thank you - saved alot of time!

@orphee4829 4 года назад

This is GOLD! I'm using the SWITCH function and a slicer to be able to choose any date dynamically. AWESOME.

@jedhansen4229 4 года назад

How do you do that?

@quinnmichael2657 4 года назад

Note for anyone who might be stuck, you need to have both relationships created, even if the one is inactive. Unsure if I missed this being stated in the video or if it was just inferred. Regardless, thanks for the video, it worked!

@GustavoCescoMiozzo 6 месяцев назад

dude, i love you.

@varing.4098 5 лет назад

Another great video. Thanks!

@andremartins1017 4 года назад

Thank you, great work.

@ssmahajan 6 лет назад

Awesome! Thanks

@MrHammy40 5 лет назад

Very good sir! Excellent explanation

@nzcali1 5 лет назад

Super helpful!!

@FredericLEGUEN-Excel 6 лет назад

Absolutely brilliant 😍👍

@annvonbelow9934 2 года назад

I'm so grateful for this video, it easily and clearly explained something that I thought was standing in the way of comprehensive reporting

@EnterpriseDNA 2 года назад

Hi Ann, glad our video helped. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

@fajnefajne4339 4 года назад

Great explanation. Thank you.

@Axeman99L 7 лет назад

Hi, Nice video. When I m using my Year slicer based on tables dates it does not show the total sales based on ship date. Is there any way that i can see both measures while selecting a year or a month from a slicer based on dates? Because if we leave it like this its not to useful? Thank you

@gxsoft 4 года назад

Very Good! Thanks, gab

@feng3625 5 лет назад

very excellent! thanks

@pesopakis 3 года назад

This just helped me a lot. Thank you so much for posting.

@EnterpriseDNA 3 года назад

Hi Panos Esopakis, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@yashashukla2678 3 года назад

This Video is a lifesaver! Great technique. Thanks a lot!

@EnterpriseDNA 3 года назад

Hi Yasha Shukla, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@medh690 3 года назад

Saved me lot of time. Thank you so much!

@EnterpriseDNA 3 года назад

Hi med h, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@MarkCBB86 6 лет назад

Very happy I watched this Video, could one use the same approach if the dataset had 2 fact tables, with shared dim tables?

@EnterpriseDNA 6 лет назад

Yes definitely. Would work the same.

@jesusalbertotrujillominaya6851 3 года назад

thanks man!

@EnterpriseDNA 3 года назад

Hi Jesus Alberto Trujillo Minaya, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@scobob 4 года назад

I am attempting to model up a similar situation but rather than multiple dates on a single fact table, I have multiple dates on multiple fact tables. For instance in the model you demonstrate, add an additional date field to your products table called "Launch Date". Add another table for Revenue Forecasts with a Forecast Date. Summarize the Actual Revenue against forecast and group by the year of Product Launch. I am attempting to use multiple master calendar date tables but getting duplicate products in each Launch Year. How would you handle this situation? For a matrix example: Launch Year > Product Name in Rows and Forecast / Actual Year in Columns with % achievement as values

@Swoopss 4 года назад

I have a question to inactive realtionships! The question is: If I do like you did with the formulafuction Userealtionsship, Do I need to have that inactive realationship or does the formula do the magic trick!

@dudejaca04 3 года назад

This is awesome

@EnterpriseDNA 3 года назад

Hi JC_0, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@pabbito 5 лет назад

and how to have 2 slicers (ie. one for order_date and one for shipment_date) having single calendar date?

@pvwetten 5 лет назад

I have the exact same question

@ganexceltamil6781 2 года назад

Thank you, it helps me lot

@EnterpriseDNA 2 года назад

Hi, glad that this video is of help! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

@nsr6 4 года назад

Great Demonstration. very useful. Could you please provide a tutorial on how to handle when there are two fact tables with different grain ? For example Actual sales vs budget .

@EnterpriseDNA 4 года назад

Hi, Please check the video on Actuals vs Budget. Here is the link: studio.ru-vid.comm6-3r3tD4nY/edit

@sharabarathan3433 3 года назад

Very nice and useful.

@EnterpriseDNA 3 года назад

Hi Shara Barathan, great that you found value in this video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@Baltie3 6 месяцев назад

Thank you, USERELATIONSHIP was a good advice but not in my case. I have to select the column with a Chiclet Slicer and in this case it is not helpful. Any hints?

@saravananpichaipillai7857 7 лет назад

SAM, thanks .. what if i need to use slicer based on multiple dates ? just create a copy of date dimension using new table and create a new active relationship???

@EnterpriseDNA 7 лет назад

Yes, you can also do it that way.

@saravananpichaipillai7857 7 лет назад

Great.. thanks

@ivanbarredo152 3 года назад

If you have 3 table visuals (Open Orders, Cancelled Order, Completed) and you need each visual to display the actual values pulled from their respective dates (ex. Order number, Customer name, Location), would it be possible to tie them to only one Calendar slicer? I'm able to use measures (count/sum/average) with the use of USERELATIONSHIP but unable to display actual values (not just numbers) in a table visual as it only allows one active relationship (ex. all data displayed is based on Open Date). Any ideas?

@EnterpriseDNA 3 года назад

Hello Ivan Barredo, thank you for posting your query onto our channel. Well it would be difficult for us to provide you the result without looking at the data and the result that you're looking for. We're providing a link below of our Community Forum where you can post your questions so that members as well as our experts team could provide you the results in a better and efficient manner. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/

@mabl4367 3 года назад

What if you want to calcuate the total sales per combination of order date and shipment date? Would you need to duplicate the date dimension for that?

@EnterpriseDNA 3 года назад

Hello Magnus Blomberg, thank you for posting your query onto our channel. Well, it's bit unclear from the question that what sort of combination you're looking for between these 2 dates. Are you trying to find out the sales that has happened between the Order Date and Ship Date? We're providing a link below where using similar type of formula you can achieve this scenario. Also we're providing a link below of our Power BI Community Forum where you can ask your questions and our members as well as expert team will be able to assist you in a better and efficient manner. Hoping you find this useful and meets your requirements that you've been looking for.🙂 Thanks and Warm Regards, Enterprise DNA ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-u8_aeLmXbVk.html forum.enterprisedna.co/

@jhazielpetersen761 Год назад

what if you need to use a slicer, which date to use? and what if you need to show it in a graph?

@EnterpriseDNA Год назад

Hello Jhaziel, You need to use a date field from the date table in a slicer visual since both the date fields from the fact tables are connected either directly or in-directly in the form of a relationship i.e., Order Date has an active relationship whereas Ship Date has an in-active relationship which is further activated via DAX function. And therefore, both these fields are connected with dates so use date field in a slicer from dates table. For further queries, you can also reach out to us at our Community Forum by providing a detailed description of your query along with relevant files for reference. Our members and team of Experts will be happy to help out! If you haven't yet, do subscribe to our RU-vid channel and LinkedIn group to keep posted on Power BI, Power Platform, and the Microsoft stack updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA www.linkedin.com/groups/12004506/

@shorn77777 6 лет назад

Could someone tell me, how to return a date value from a column in a pivot

@EnterpriseDNA 6 лет назад

Not 100% sure what your asking. Are you wanting to return an actual date? Look into the function SELECTEDVALUE. This might solve it.

@avalondsouza2002 6 лет назад

This is a good solution. There are some limitations I've encountered with this method. For example, if I wanted to add a filter in a calculate statement that uses userrelationship, the filter uses the old relationship rather than the userrelationsip one in the calculate statement. Ex: CALCULATE( [Total Sales], USERRELATIONSHIP('Date Table'[Date], 'Fact Sales'[Inactive Relationship Date], FILTER(ALL('Date Table', ......)) In this case the userrelationship does not get utilized in the filter function. Hence the filter would be on the active relationship date column, rather than the inactive one.

@EnterpriseDNA 6 лет назад

What you want to do there is wrap the CALCULATE with the FILTER inside another CALCULATE with USERELATIONSHIP. So the USERELATIONSHIP is in the OUTER one. So you formula would start with CALCULATE( CALCULATE( ......

@avalondsouza2002 6 лет назад

@@EnterpriseDNA Ahh that makes sense. Will try that out. I initially did the calculate (calculate..)) but put the user relationship on the outer calculate filter. Thanks for the suggestion

@paulharrison8652 3 года назад

This looks very helpful, but could you explain why the totals for the two measures at the bottom of the table are different when you are summing up the same column?

@paulharrison8652 3 года назад

Sorry, I've just realised that perhaps some of the orders haven't been shipped yet and therefore don't have a ship date to filter on.

@EnterpriseDNA 3 года назад

Hi Paul Harrison, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@sachithapali6619 2 года назад

Hi I work in the hospitality industry, we have to work with multiple dates all the time (Booked dates and stay dates) however even after watching this video multiple times I can't understand how to derive a stay date value for this year against the same period last year for same booked dates (Ex: Stay data for June 2022, booked till Jan 2022 against stay data for June 2021, booked till Jan 2021). Appreciate some help.

@EnterpriseDNA 2 года назад

Hello Sachitha, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post. Well, it's always a difficult task to provide the results or comments without looking at the data, it's structure and working of the PBIX file for the reference. And therefore we request you to please write back to us onto our Power BI Community Forum by providing the exact description of the problem that you're facing in along with the working or demo PBIX files for the reference where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the Community Forum provided for the reference purposes. Hoping you find this useful. Also, make sure that you're subscribed to our RU-vid channel so that you don't miss out on any updates pertaining to the Power BI. You can also join our Power BI group on LinkedIn to receive latest updates on Power BI. Below are the links provided for the reference purposes. Cheers, Enterprise DNA forum.enterprisedna.co/ ru-vid.com www.linkedin.com/groups/12004506/

@muhammadghazi994 3 года назад

I got the concept but somebody help me with dates in same table as this doesnt work if we are comparing dates from same table.

@EnterpriseDNA 3 года назад

Hello Muhammad Ghazi, thank you for posting your query onto our channel. While your question is not clear to us in precise context but still we'll try our best to provide you with the solution. When you're comparing dates from the same table in this case mostly you would require to use a DATEDIFF() function. We're providing few of the links of the articles from our blog posts as well as links of the videos from our Enterprise DNA RU-vid channel pertaining to this topic. For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference as well. Hoping you find this useful and meets your requirements that you've been looking for. Also make sure that you're subscribed to our RU-vid channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning!!! forum.enterprisedna.co/ ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Q8iri3G1_x4.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kRACuS4eKWA.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-v88mEK0Fhkg.html forum.enterprisedna.co/t/datediff-to-only-calculate-number-of-work-days/3627 forum.enterprisedna.co/t/calculating-difference-between-dates-in-same-column-with-multiple-events-on-same-day/2633

@jackyo9841 3 года назад

I'm not sure what I did wrong i created the Dates table and when dragging the Dates to the table visual, all i got is a single row with total sum, unlike the the sum each individual date or period. i can't find what I'm doing wrong and I'm using a pretty simple data set for testing.

@EnterpriseDNA 3 года назад

Hello Jacky O, thank You for posting your query onto our channel. Well it's difficult for us to provide you the results without looking at your file about where you've actually faltered. We request you to raise a query onto our Community Forum so that our members as well as the experts team can provide you the results in better and efficient manner. Below is the link provided for the reference. Hoping you find this useful. Happy Learning!!! forum.enterprisedna.co/

@jackyo9841 3 года назад

@@EnterpriseDNA Thanks for the response! I've signed up, seems still awaiting approval. I've 'fixed' my problem and root cause isn't clear though. I've re-import the data and issue seems to go-away. might have been data corruptions as I've been doing some actions to the data. All good and good learning experience

@kadirkkkk 3 года назад

I did not understand why the totals are not equal

@andrew260z 4 года назад

wish i found this earlier

@EnterpriseDNA 4 года назад

Now you did, hopefully it'll help :)

@RavenRock88 3 года назад

THIS IS HOT!

@EnterpriseDNA 3 года назад

Hi RavenRock88, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: ru-vid.com/show-UCy2rBgj4M1tzK-urTZ28zcA

@jenlam4466 Год назад

DATE IS THE WORST THING ON POWER BI!!!!!!!

@EnterpriseDNA Год назад

Hi Jen, it can be tricky but we hope our content can help you out! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

@jenlam4466 Год назад

@@EnterpriseDNA Hi, I have a membership start date connected to the date table but why not all the months are appearing even though the x-axis type is continuous? It is currently showing only Jan, Mar, May, Jul, Sep and Nov?