***** Learning Power BI? ***** FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-reference-guide-download/ FREE - Power BI Resources - enterprisedna.co/power-bi-resources Enterprise DNA Online - www.enterprisedna.co/ Enterprise DNA Membership - enterprisedna.co/membership Enterprise DNA Events - enterprisedna.co/enterprise-dna-events-page/ ***** Related Links ***** How To Work With Multiple Dates In Power BI - blog.enterprisedna.co/working-with-multiple-dates-in-power-bi/ Master Virtual Tables In Power BI Using DAX - blog.enterprisedna.co/working-with-virtual-in-memory-tables-in-power-bi-using-dax/ Discover Multiple Product Purchases Using DAX In Power BI - blog.enterprisedna.co/discover-the-amount-of-customers-who-purchase-multiple-products-w-power-bi/ ***** Related Course Modules ***** Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-calculations DAX Formula Deep Dives - portal.enterprisedna.co/p/dax-formula-deep-dives Ultimate Beginners Guide to DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax ***** Related Support Forum Posts ***** How To Calculate Multiple Rows For A Condition - forum.enterprisedna.co/t/how-to-calculate-multiple-rows-for-a-condition/2698 Filter By Multiple Dates In The Same Table - forum.enterprisedna.co/t/filter-by-multiple-dates-in-the-same-table/4204 One Dimension, Multiple Facts And Multiple Interconnected Facts - forum.enterprisedna.co/t/one-dimension-multiple-facts-and-multiple-interconnected-facts/5238 For more multiple date calculations support queries to review see here…. - forum.enterprisedna.co/search?q=multiple%20date%20calculations
Hi Daniel, 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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Great video, thanks! It was just what I was looking for. A couple of things: the FILTER functions presumably have some kind of implicit AND operation between them?; I was interested that you commented that you didn't require the relationships at all for this to work. Why is that?
Hi Mathieu Lussier, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hy! Thanks for the great Video. I need exactly what you describe in your video, but some of my "expiring dates" are empty. In my case I want to analyze open support cases of IT helpdesk. I have a created date and a closing date but closing date is empty until suport case is closed. So in this case I do not get the right result. What can I do? Thanks for your help.
Commenting @ 0:50 (Problem statement) before viewing the rest of the video just to see if what I am proposing could be a solution too or if the same solution is proposed in the video. Fingers crossed. The order is moving thru several stages of the process and can be at any one stage at a given date. I guess the problem could be solved by having a column to identify the each stage and from there Max(date) of a given order no will locate it additional info on process stage. Having stages id'ed will also help in determining how many orders are stuck at which process stage at a given date. I can visualize historical linechart trends by process stage.
What if you want the user to be able to slice on two different dates at the same time? The user want to see the orders created in jan and shipped in feb. Would that require you to duplicate the date dimension?
Hello Magnus Blomberg, thank you for posting your query onto our channel. Well, to achieve this type of scenario. I guess you'll require 2 date tables to slice and dice because single date table cannot show you two different scenarios at the same time. For furthermore clarity on this, you can ask the query onto our Power BI Forum wherein our members as well as experts will be able to help you better and provide an solution in an efficient manner. We're providing a link below of our community forum. Happy Learning!!! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/
Many thanks! As always great videos! I would like to ask one question. I did everything the same as in a video. I have 4 inactive relation. My calendar table start from the earliest date out of 4 columns (for which I create inactive relations). But when I create a slicer with a Year or Month Name from my Calendar table, its shows value Blank. And I don't know why. Is it possible do not show blank in slicer? Or this happened due to a lot of inactive relations?
Sam, I was excited to hear you state the example of "How many days has this project been going" in your intro. Unfortunately your example didn't cover that scenario, which I think differs from summing sales between dates. Even after setting up my model as you stated, I still cannot figure out how to get the USERELATIONSHIP to fit into my situation. In short I have a fact table with 3 different milestone dates for every project on a single row. I can't use durations directly between those dates (DATE1-DATE2) because that would be in calendar days and I need to calculate Business days. I have my business days identified in my calendar table and want to count the number of business days that occurred between 2 of the milestone dates using various IF logic.... Example would be: BusinessDays between DATE1 and DATE 2 or BusinessDays between DATE1 and DATE3. I can sort out all of the if logic not mentioned here, but can't grasp how to identify a date in the fact table, move to the Calendar table, count the "1's" that identify them as Business days and then stop at a date back on my fact table with a counted result. Any help on this scenario would be GREATLY appreciated.
Nathan, I hope I understood this correctly. What I have done in similar situations is create a simple measure that sums the business days (e.g. BuDays = sum(column with 0s/1s)). Then do a CALCULATE(BuDays, filter(dates, datesMAX(DATE 1)) This will add up the business days only between the 2 dates specified. Hope this helps
Thanks a lot!! I have a data set with subscriber data. For every row I have sales date and cancellation date. Active users have a null value for cancellation date. If I set up my model like yours, can i calculate active users per date? Total sales per month? Cancellation per month?
Hi Kenneth Stephani, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
yes - userrelationship dax can help - in this example, unclear not clear why you could't simply set up a CountRow function with embedded filter statement that used the 'Ref Type' as as filter expression.
Hello Drew Bbc, Thank You for question. We appreciate your time. The measure which has been referenced as - "Total Orders" is based on "COUNTROWS()" function itself i.e., "COUNTROWS( Sales )". For the question related to why we're not using "USERELATIONSHIP()" function in this case. It's because this function will activate the relationship only between one of the columns and here in this case we need to evaluate the results between the two dates and not based on one single date. And when we need to calculate the results between the two dates, "USERELATIONSHIP()" function is not the right choice in that case. 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 TH-cam channel pertaining to this topic. blog.enterprisedna.co/how-many-staff-do-we-currently-have-multiple-dates-logic-in-power-bi-using-dax/ th-cam.com/video/WqWAML9cQ6M/w-d-xo.html th-cam.com/video/8ozINFvuiUg/w-d-xo.html For more queries, you can also reach out to us through our Community Forum where our members as well as Experts Team will be able to assist you, in a better and efficient manner. I hope this helps.🙂 Please make sure that you're subscribed to our TH-cam channel so you don't miss out any updates regarding Power BI. Along with it, you may also be interested to join our Power BI group on LinkedIn to receive latest updates. Links provided below for your reference: Happy Learning!!! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
One question is that I've seen you always have Date type as the PK of Dates dim, instead of that Datekey column which might be 8 digit integer, is there any reason behind this? Because I always prefer to use integer in DB layer as the Date dim PK
interesting. but how do you handle this when you have 2 fact table? I have 2 fact tables Fact_Sales Fact_Promo
2 conforming dims Dim_Customer Dim_Date
Not all customers in Fact_Sales are in Fact_Promo Therefore if I want to see all sales by customers in Fact_Promo, this measure will work:
VAR _Result = CALCULATE( [Total Sales] , CROSSFILTER ( dim_Customer[PK_Cust_ID] , Fact_Promo[PK_Cust_ID] , Both ) )
RETURN _Result
What I really need is all sales for each customer in Fact_Promo where: Fact_Sales[Date_Order] is between Fact_Promo[Date_Start] and Fact_Promo[Date_End]
Anybody know what to do if I want to use the datediff function across two different tables? I am sure it is complex, especially since one tables dates start in 2015 while the other starts in 2000. Please direct me in the right direction. Maybe it is even a different function I need to use. I also need to create this as a calculated column.
Hello Ted, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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. Hoping you find this useful! You can subscribe to our TH-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
How to compare Employee Master with Attendance Transaction for all dates in a month in Power BI. Who All Employees has not marked their attendance with date details
Hello Amar, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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. Hoping you find this useful. Subscribe to our TH-cam 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. Cheers, Enterprise DNA forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Hello urmumlel, thank you for posting your query onto our channel. Firstly, your query is not clear to us and secondly, it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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. Hoping you find this useful. Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning!!! forum.enterprisedna.co/ th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
***** Learning Power BI? *****
FREE COURSE - Ultimate Beginners Guide To Power BI - portal.enterprisedna.co/p/ultimate-beginners-guide-to-power-bi
FREE COURSE - Ultimate Beginners Guide To DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
FREE - 60 Page DAX Reference Guide Download - enterprisedna.co/dax-formula-reference-guide-download/
FREE - Power BI Resources - enterprisedna.co/power-bi-resources
Enterprise DNA Online - www.enterprisedna.co/
Enterprise DNA Membership - enterprisedna.co/membership
Enterprise DNA Events - enterprisedna.co/enterprise-dna-events-page/
***** Related Links *****
How To Work With Multiple Dates In Power BI - blog.enterprisedna.co/working-with-multiple-dates-in-power-bi/
Master Virtual Tables In Power BI Using DAX - blog.enterprisedna.co/working-with-virtual-in-memory-tables-in-power-bi-using-dax/
Discover Multiple Product Purchases Using DAX In Power BI - blog.enterprisedna.co/discover-the-amount-of-customers-who-purchase-multiple-products-w-power-bi/
***** Related Course Modules *****
Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-calculations
DAX Formula Deep Dives - portal.enterprisedna.co/p/dax-formula-deep-dives
Ultimate Beginners Guide to DAX - portal.enterprisedna.co/p/ultimate-beginners-guide-to-dax
***** Related Support Forum Posts *****
How To Calculate Multiple Rows For A Condition - forum.enterprisedna.co/t/how-to-calculate-multiple-rows-for-a-condition/2698
Filter By Multiple Dates In The Same Table - forum.enterprisedna.co/t/filter-by-multiple-dates-in-the-same-table/4204
One Dimension, Multiple Facts And Multiple Interconnected Facts - forum.enterprisedna.co/t/one-dimension-multiple-facts-and-multiple-interconnected-facts/5238
For more multiple date calculations support queries to review see here…. - forum.enterprisedna.co/search?q=multiple%20date%20calculations
I love that this is 5 years old and still relevant! Thanks a lot for the tutorial.
This is exactly what I needed clarification on. Great video.
Hi Daniel, 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: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Great video, thanks! It was just what I was looking for. A couple of things: the FILTER functions presumably have some kind of implicit AND operation between them?; I was interested that you commented that you didn't require the relationships at all for this to work. Why is that?
Thanks for the video!! I had a problem like this and i wasnt finding anything similar in the internet!!
Thanks!! Did the same this week but we calculate columns. This looks better :-)
Very helpful. I was trying to solve for many days. No one was able to solve it out.
Glad it was helpful! Please dont forget to subscribe to our channel :)
Thats a great video and I would like to know how RLS works on this.
This video saved my life.
Hi Mathieu Lussier, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
Hy! Thanks for the great Video. I need exactly what you describe in your video, but some of my "expiring dates" are empty. In my case I want to analyze open support cases of IT helpdesk. I have a created date and a closing date but closing date is empty until suport case is closed. So in this case I do not get the right result. What can I do? Thanks for your help.
I have the exact same question.
Awesome , awesome videos !
Commenting @ 0:50 (Problem statement) before viewing the rest of the video just to see if what I am proposing could be a solution too or if the same solution is proposed in the video. Fingers crossed. The order is moving thru several stages of the process and can be at any one stage at a given date. I guess the problem could be solved by having a column to identify the each stage and from there Max(date) of a given order no will locate it additional info on process stage.
Having stages id'ed will also help in determining how many orders are stuck at which process stage at a given date.
I can visualize historical linechart trends by process stage.
What if you want the user to be able to slice on two different dates at the same time? The user want to see the orders created in jan and shipped in feb.
Would that require you to duplicate the date dimension?
Hello Magnus Blomberg,
thank you for posting your query onto our channel.
Well, to achieve this type of scenario. I guess you'll require 2 date tables to slice and dice because single date table cannot show you two different scenarios at the same time.
For furthermore clarity on this, you can ask the query onto our Power BI Forum wherein our members as well as experts will be able to help you better and provide an solution in an efficient manner. We're providing a link below of our community forum.
Happy Learning!!!
Thanks and Warm Regards,
Enterprise DNA
forum.enterprisedna.co/
Many thanks! As always great videos! I would like to ask one question. I did everything the same as in a video. I have 4 inactive relation. My calendar table start from the earliest date out of 4 columns (for which I create inactive relations). But when I create a slicer with a Year or Month Name from my Calendar table, its shows value Blank. And I don't know why. Is it possible do not show blank in slicer? Or this happened due to a lot of inactive relations?
Sam, I was excited to hear you state the example of "How many days has this project been going" in your intro. Unfortunately your example didn't cover that scenario, which I think differs from summing sales between dates. Even after setting up my model as you stated, I still cannot figure out how to get the USERELATIONSHIP to fit into my situation. In short I have a fact table with 3 different milestone dates for every project on a single row. I can't use durations directly between those dates (DATE1-DATE2) because that would be in calendar days and I need to calculate Business days. I have my business days identified in my calendar table and want to count the number of business days that occurred between 2 of the milestone dates using various IF logic.... Example would be: BusinessDays between DATE1 and DATE 2 or BusinessDays between DATE1 and DATE3. I can sort out all of the if logic not mentioned here, but can't grasp how to identify a date in the fact table, move to the Calendar table, count the "1's" that identify them as Business days and then stop at a date back on my fact table with a counted result. Any help on this scenario would be GREATLY appreciated.
Nathan, I hope I understood this correctly. What I have done in similar situations is create a simple measure that sums the business days (e.g. BuDays = sum(column with 0s/1s)). Then do a CALCULATE(BuDays, filter(dates, datesMAX(DATE 1)) This will add up the business days only between the 2 dates specified. Hope this helps
Thanks a lot!! I have a data set with subscriber data. For every row I have sales date and cancellation date. Active users have a null value for cancellation date.
If I set up my model like yours, can i calculate active users per date? Total sales per month? Cancellation per month?
Hi, for help like this check out the Enterprise DNA Support Forum for some ideas on this - forum.enterprisedna.co/search?expanded=true&q=Subscription
Excellent video!
Hi Kenneth Stephani, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials.
Here’s the link: th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
yes - userrelationship dax can help - in this example, unclear not clear why you could't simply set up a CountRow function with embedded filter statement that used the 'Ref Type' as as filter expression.
Hello Drew Bbc,
Thank You for question. We appreciate your time.
The measure which has been referenced as - "Total Orders" is based on "COUNTROWS()" function itself i.e., "COUNTROWS( Sales )".
For the question related to why we're not using "USERELATIONSHIP()" function in this case. It's because this function will activate the relationship only between one of the columns and here in this case we need to evaluate the results between the two dates and not based on one single date. And when we need to calculate the results between the two dates, "USERELATIONSHIP()" function is not the right choice in that case.
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 TH-cam channel pertaining to this topic.
blog.enterprisedna.co/how-many-staff-do-we-currently-have-multiple-dates-logic-in-power-bi-using-dax/
th-cam.com/video/WqWAML9cQ6M/w-d-xo.html
th-cam.com/video/8ozINFvuiUg/w-d-xo.html
For more queries, you can also reach out to us through our Community Forum where our members as well as Experts Team will be able to assist you, in a better and efficient manner.
I hope this helps.🙂
Please make sure that you're subscribed to our TH-cam channel so you don't miss out any updates regarding Power BI. Along with it, you may also be interested to join our Power BI group on LinkedIn to receive latest updates. Links provided below for your reference:
Happy Learning!!!
Thanks and Warm Regards,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
One question is that I've seen you always have Date type as the PK of Dates dim, instead of that Datekey column which might be 8 digit integer, is there any reason behind this? Because I always prefer to use integer in DB layer as the Date dim PK
interesting. but how do you handle this when you have 2 fact table? I have 2 fact tables
Fact_Sales
Fact_Promo
2 conforming dims
Dim_Customer
Dim_Date
Not all customers in Fact_Sales are in Fact_Promo
Therefore if I want to see all sales by customers in Fact_Promo, this measure will work:
VAR _Result = CALCULATE(
[Total Sales]
, CROSSFILTER (
dim_Customer[PK_Cust_ID]
, Fact_Promo[PK_Cust_ID]
, Both
)
)
RETURN
_Result
What I really need is all sales for each customer in Fact_Promo where:
Fact_Sales[Date_Order] is between Fact_Promo[Date_Start] and Fact_Promo[Date_End]
Anybody know what to do if I want to use the datediff function across two different tables? I am sure it is complex, especially since one tables dates start in 2015 while the other starts in 2000. Please direct me in the right direction. Maybe it is even a different function I need to use. I also need to create this as a calculated column.
Hello Ted,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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.
Hoping you find this useful! You can subscribe to our TH-cam channel so that you won't miss out on any Power BI updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
How to compare Employee Master with Attendance Transaction for all dates in a month in Power BI. Who All Employees has not marked their attendance with date details
Hello Amar,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
It's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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.
Hoping you find this useful. Subscribe to our TH-cam 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.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Still does not work for me. My measure gives the same output as if I were to just not do anything and have total count. Please help.
Hello urmumlel,
thank you for posting your query onto our channel.
Firstly, your query is not clear to us and secondly, it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference.
And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve 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.
Hoping you find this useful.
Also make sure that you're subscribed to our TH-cam channel so that you don't miss out on any updates pertaining to the Power BI.
Happy Learning!!!
forum.enterprisedna.co/
th-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html