***** 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 ***** Showing Cumulative Results Versus Targets Only To Last Sales Date In Power BI - blog.enterprisedna.co/showing-actual-results-vs-targets-only-to-last-sales-date-in-power-bi/ Showing Difference Between Sales And Budgets To Date - Forecasting In Power BI - blog.enterprisedna.co/showing-difference-between-sales-and-budgets-to-date-forecasting-in-power-bi/ Compare Actual Results To Budgets Using Power BI Forecasting - blog.enterprisedna.co/compare-actual-results-to-budgets-or-forecasts-in-power-bi/ ***** Related Course Modules ***** Budgeting & Forecasting - portal.enterprisedna.co/p/budgeting-and-forecasting Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-calculations Mastering DAX Calculations - portal.enterprisedna.co/p/mastering-dax-calculations ***** Related Support Forum Posts ***** Budgeting Allocation, Material - forum.enterprisedna.co/t/budgeting-allocation-material/788 Getting Totals Correct When Using The Budget Allocation Formula - forum.enterprisedna.co/t/getting-totals-correct-when-using-the-budget-allocation-formula/313 Allocating Budget - Granularity? - forum.enterprisedna.co/t/allocating-budget-granularity/641 For more budget allocation support queries to review see here…. - forum.enterprisedna.co/search?q=budget%20allocation
@3:37 Can you create the relationship between dates if we change the "Month & Year" format of the "Sales Budgets" Table to match the format of "Date" in the "Dates" table?
Hi @covid6992, No, it's not possible even if you change the format and then create a relationship. That's because in this case, budgets are still at a monthly granular level whereas dates are at a daily granular level and upon creating an active relationship with the dates field from dates table, all the results will be attributed to that single day rather than apportioning the budgeted values at a daily level. And why do you want to create an active relationship, in this case, when a results can be achieved via virtual relationship using the "TREATAS()" function as showcased in the given video. If your organization has created the budgets at a daily granular level then you can create an active relationship between the Budgets and the Dates table. We request you to please go through our "Data Transformations and Modelling" course which is available onto our Enterprise DNA education portal to better understand the concepts. 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 UA-cam channel pertaining to the topic. - "TREATAS() DAX function". 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 UA-cam channel and LinkedIn group to keep posted on the latest data skills and tools updates. Hoping you find this useful! Cheers, Enterprise DNA forum.enterprisedna.co/ www.youtube.com/@EnterpriseDNA www.linkedin.com/groups/12004506/ blog.enterprisedna.co/how-to-use-the-treatas-function-power-bi-and-dax/ blog.enterprisedna.co/how-to-create-virtual-relationships-using-treatas-in-power-bi/ blog.enterprisedna.co/power-bi-userelationship-vs-treatas/ ua-cam.com/video/EcnSuMKgRXA/v-deo.html ua-cam.com/video/Ea0VBWw8-mg/v-deo.html ua-cam.com/video/SJhpZ9N3DVA/v-deo.html
Hello Stoneage8988, Your presumption is correct in case the business doesn't operate during weekends. In our scenario, we've presumed that business runs all 7 days a week and therefore, measure is written and budgets are allocated accordingly. In case, your business doesn't operate during weekends then adjust the measure by incorporating the weekends context in it where we already have a weekends column in our dedicated date table. For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, 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! If you haven't yet, you can subscribe to our UA-cam channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI. Cheers, Enterprise DNA forum.enterprisedna.co/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Thanks for the tutorial! I'm trying to make a MTD budjet, but keep getting only a flat line on a month view. Any tip to use this calculation with cumulative MTD?
Hi Jenna! Cumulative MTD has been discussed countless times in our Support Forum. You may browse over the discussions here: forum.enterprisedna.co/search?q=cumulative%20mtd
Hi,can we create forecast value with out phisical value for some months and can you advise how to create future month fore based latest available actual
Hi koteswari, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. Well 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. But 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 UA-cam channel pertaining to this topic. Hoping you find this useful! You can subscribe to our UA-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/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/ blog.enterprisedna.co/forecasting-techniques-in-power-bi-using-dax-formula/ blog.enterprisedna.co/create-dynamic-forward-forecasts-in-power-bi-w-dax/ community.powerbi.com/t5/Community-Blog/Prediction-Techniques-for-Power-BI-How-to-Set-Up-This-Type-of/ba-p/1150018 ua-cam.com/video/vq3VOERJw7s/v-deo.html ua-cam.com/video/U7sl7guoxMI/v-deo.html ua-cam.com/video/njjEkkoyxl0/v-deo.html
@@EnterpriseDNA Hi sir,assume i have data Jan=100,Feb=200 and i want to calculate mar value based on Feb value with formula divide(Feb value,Feb month num)+Feb value. For Apr i want to calculate based mar value Like that i want to calculate till dec
Hey, this is a great tutorial. Thanks, got my model working - however I have the data at a territory level (in budget and sales) - how do I link these two together?
One issue I find with this is the total at the buttom of say a table is then an average daily value instead of the sum of each days allocated budget. Any work around? Actually the average at buttom is not a real average if you do not select whole months to view.
Hello Alexander, Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it. So we're presuming that the problem you're facing is pertaining to the fixing of grand totals. In that case, we're providing a link of one of the post from our Community Forum which our expert had created to address this type of issue. 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! You can subscribe to our UA-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/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/ forum.enterprisedna.co/t/fix-incorrect-totals/8834
Hi Sam great work, great example but i have question, how to did you convert Month & Year into like January 2018, what is the origin format of column, i mean in my case my budget calendar column actually looks like {01/01/2018, 01/02/2018, 01/03/2018}, and i just changed format to like {January 2018, February 2018, March 2018} and things didnt work on me. Could you help me with this issue thanks in advance.
Hello Ismail K, Thank you for question. We appreciate your time. We didn't convert anything in a format, that data was imported from an Excel file. Even if you want to convert the data into that specific format, you can write the formula provided below. Please note it's a generalized formula, so you'll be required to adapt the naming conventions as per your file - Date Format = FORMAT( Date[Month_Name] , "MMMM" ) & " " & FORMAT( Date[Year] , "YYYY" ) 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 UA-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/ ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html www.linkedin.com/groups/12004506/
Hi Sam is there anyway to do a "Lookupvalue" formulation in powerpivot where you can add an argument for the lookup array which dictates the column it brings back from another table?
Hello Scopse G, Thank You for posting your query onto our channel. Well we haven't explored the LOOKUPVALUE function under the power pivot since we mainly provide our solutions in line with the Power BI. But below we're providing a link for the reference where you can find an article regarding the use of LOOKUPVALUE function in the Power Pivot. Hoping this article helps you and meets your requirements that you've been looking.🙂 Thanks and Warm Regards, Enterprise DNA www.sumproduct.com/blog/article/power-pivot-principles/ppp-introducing-the-function-lookupvalue
You essentially did the same thing from the first video. Lets say I need to budget by month and then further by category (food, gas, living) how would I do that?
Hi Juan, please find same scenario in our Enteprirse DNA Forum to get the answer that you need. forum.enterprisedna.co/search?q=seasonality%20by%20days Please watch out for our 30 for 30 this month, that's 30 videos for 30 days! Don't forget to subscribe to our channel :)
Hi Sam, I’ve seen this already, thanks. I have a number of questions regarding the membership, suitability for my requirements, before committing to the purchase. Do you have a contact email? Many thanks
In ideal world you got such a data. Wish I can implement it as it so. Reality is most of the time totally different and you got data all over the places and budget, haha, definitely not like that. But good video as usual.
![Calculating Average Monthly Purchases In Power BI Using DAX [2023 Update]](http://i.ytimg.com/vi/eNnSUdo4UNU/mqdefault.jpg)
***** 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 *****
Showing Cumulative Results Versus Targets Only To Last Sales Date In Power BI - blog.enterprisedna.co/showing-actual-results-vs-targets-only-to-last-sales-date-in-power-bi/
Showing Difference Between Sales And Budgets To Date - Forecasting In Power BI - blog.enterprisedna.co/showing-difference-between-sales-and-budgets-to-date-forecasting-in-power-bi/
Compare Actual Results To Budgets Using Power BI Forecasting - blog.enterprisedna.co/compare-actual-results-to-budgets-or-forecasts-in-power-bi/
***** Related Course Modules *****
Budgeting & Forecasting - portal.enterprisedna.co/p/budgeting-and-forecasting
Time Intelligence Calculations - portal.enterprisedna.co/p/time-intelligence-calculations
Mastering DAX Calculations - portal.enterprisedna.co/p/mastering-dax-calculations
***** Related Support Forum Posts *****
Budgeting Allocation, Material - forum.enterprisedna.co/t/budgeting-allocation-material/788
Getting Totals Correct When Using The Budget Allocation Formula - forum.enterprisedna.co/t/getting-totals-correct-when-using-the-budget-allocation-formula/313
Allocating Budget - Granularity? - forum.enterprisedna.co/t/allocating-budget-granularity/641
For more budget allocation support queries to review see here…. - forum.enterprisedna.co/search?q=budget%20allocation
@3:37 Can you create the relationship between dates if we change the "Month & Year" format of the "Sales Budgets" Table to match the format of "Date" in the "Dates" table?
Hi @covid6992,
No, it's not possible even if you change the format and then create a relationship. That's because in this case, budgets are still at a monthly granular level whereas dates are at a daily granular level and upon creating an active relationship with the dates field from dates table, all the results will be attributed to that single day rather than apportioning the budgeted values at a daily level.
And why do you want to create an active relationship, in this case, when a results can be achieved via virtual relationship using the "TREATAS()" function as showcased in the given video. If your organization has created the budgets at a daily granular level then you can create an active relationship between the Budgets and the Dates table. We request you to please go through our "Data Transformations and Modelling" course which is available onto our Enterprise DNA education portal to better understand the concepts.
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 UA-cam channel pertaining to the topic. - "TREATAS() DAX function".
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 UA-cam channel and LinkedIn group to keep posted on the latest data skills and tools updates.
Hoping you find this useful!
Cheers,
Enterprise DNA
forum.enterprisedna.co/
www.youtube.com/@EnterpriseDNA
www.linkedin.com/groups/12004506/
blog.enterprisedna.co/how-to-use-the-treatas-function-power-bi-and-dax/
blog.enterprisedna.co/how-to-create-virtual-relationships-using-treatas-in-power-bi/
blog.enterprisedna.co/power-bi-userelationship-vs-treatas/
ua-cam.com/video/EcnSuMKgRXA/v-deo.html
ua-cam.com/video/Ea0VBWw8-mg/v-deo.html
ua-cam.com/video/SJhpZ9N3DVA/v-deo.html
@@EnterpriseDNA I appreciate the detailed response!
You are a wizard, sir! Thank you for this valuable resource!
regarding the days in the month, isn't it more accurate to exclude weekends ? there are around 22 working days each month, instead of 30~31...
Hello Stoneage8988,
Your presumption is correct in case the business doesn't operate during weekends. In our scenario, we've presumed that business runs all 7 days a week and therefore, measure is written and budgets are allocated accordingly.
In case, your business doesn't operate during weekends then adjust the measure by incorporating the weekends context in it where we already have a weekends column in our dedicated date table.
For furthermore queries, you can also reach out to us onto our Community Forum by providing a proper description of the query along with the reference mock-up files to work on, 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! If you haven't yet, you can subscribe to our UA-cam channel so that you won't miss out on any Power BI & Power Platform updates. You can also join our LinkedIn group to receive latest updates on Power BI.
Cheers,
Enterprise DNA
forum.enterprisedna.co/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Thanks for the tutorial! I'm trying to make a MTD budjet, but keep getting only a flat line on a month view. Any tip to use this calculation with cumulative MTD?
Hi Jenna! Cumulative MTD has been discussed countless times in our Support Forum. You may browse over the discussions here: forum.enterprisedna.co/search?q=cumulative%20mtd
Hi,can we create forecast value with out phisical value for some months and can you advise how to create future month fore based latest available actual
Hi koteswari,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
Well 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.
But 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 UA-cam channel pertaining to this topic.
Hoping you find this useful! You can subscribe to our UA-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/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
blog.enterprisedna.co/forecasting-techniques-in-power-bi-using-dax-formula/
blog.enterprisedna.co/create-dynamic-forward-forecasts-in-power-bi-w-dax/
community.powerbi.com/t5/Community-Blog/Prediction-Techniques-for-Power-BI-How-to-Set-Up-This-Type-of/ba-p/1150018
ua-cam.com/video/vq3VOERJw7s/v-deo.html
ua-cam.com/video/U7sl7guoxMI/v-deo.html
ua-cam.com/video/njjEkkoyxl0/v-deo.html
@@EnterpriseDNA Hi sir,assume i have data Jan=100,Feb=200 and i want to calculate mar value based on Feb value with formula divide(Feb value,Feb month num)+Feb value. For Apr i want to calculate based mar value Like that i want to calculate till dec
Thanks a lot! I was looking for this solution some time. Thanks again :-)
How do I instead of days in a month, change it to working days in a month?
Hey, this is a great tutorial. Thanks, got my model working - however I have the data at a territory level (in budget and sales) - how do I link these two together?
Hi Robert, see here for some ideas on this - forum.enterprisedna.co/search?q=Link%20Budget%20and%20Sales
One issue I find with this is the total at the buttom of say a table is then an average daily value instead of the sum of each days allocated budget. Any work around?
Actually the average at buttom is not a real average if you do not select whole months to view.
Hello Alexander,
Thank you for posting your query onto our channel. We really appreciate you taking your valuable time to post it.
So we're presuming that the problem you're facing is pertaining to the fixing of grand totals. In that case, we're providing a link of one of the post from our Community Forum which our expert had created to address this type of issue.
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! You can subscribe to our UA-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/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
forum.enterprisedna.co/t/fix-incorrect-totals/8834
Hi Sam great work, great example but i have question, how to did you convert Month & Year into like January 2018, what is the origin format of column, i mean in my case my budget calendar column actually looks like {01/01/2018, 01/02/2018, 01/03/2018}, and i just changed format to like {January 2018, February 2018, March 2018} and things didnt work on me. Could you help me with this issue thanks in advance.
Hello Ismail K,
Thank you for question. We appreciate your time.
We didn't convert anything in a format, that data was imported from an Excel file. Even if you want to convert the data into that specific format, you can write the formula provided below. Please note it's a generalized formula, so you'll be required to adapt the naming conventions as per your file -
Date Format =
FORMAT( Date[Month_Name] , "MMMM" ) & " " & FORMAT( Date[Year] , "YYYY" )
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 UA-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/
ua-cam.com/channels/y2rBgj4M1tzK-urTZ28zcA.html
www.linkedin.com/groups/12004506/
Hi Sam is there anyway to do a "Lookupvalue" formulation in powerpivot where you can add an argument for the lookup array which dictates the column it brings back from another table?
Hello Scopse G,
Thank You for posting your query onto our channel.
Well we haven't explored the LOOKUPVALUE function under the power pivot since we mainly provide our solutions in line with the Power BI.
But below we're providing a link for the reference where you can find an article regarding the use of LOOKUPVALUE function in the Power Pivot.
Hoping this article helps you and meets your requirements that you've been looking.🙂
Thanks and Warm Regards,
Enterprise DNA
www.sumproduct.com/blog/article/power-pivot-principles/ppp-introducing-the-function-lookupvalue
You essentially did the same thing from the first video. Lets say I need to budget by month and then further by category (food, gas, living) how would I do that?
Hey Grant,
See here for ideas on this forum.enterprisedna.co/search?q=Monthly%20Budgets%20Category
All the best,
Enterprise DNA
Hi Sam. Is this able to be filtered by weekday using my "weekday" "weekend" measure in my date table as I only want to allocate this mon-fri
Hi Craig, check out the Enterprise DNA Support Forum for some ideas on this - forum.enterprisedna.co/t/comparison-code-doesnt-work/5653/4
Hello any tip to do it with seasonality by days? ex. Friday has more sales than monday
Hi Juan, please find same scenario in our Enteprirse DNA Forum to get the answer that you need.
forum.enterprisedna.co/search?q=seasonality%20by%20days
Please watch out for our 30 for 30 this month, that's 30 videos for 30 days! Don't forget to subscribe to our channel :)
What if I missed the event? Are your videos from the learning summit posted to view?
Some are still up on youtube. Will be moved here shortly - portal.enterprisedna.co/
where can i get the data file?
Hi Sam, would it be possible to use a YTD sum on this measure? Not getring this working for me. Any tips?
See here for some ideas - ua-cam.com/video/-LXQJU6po_Y/v-deo.html
Great, got it working fine now. Thanks a lot! Great channel, great insights and great for inspiration.
Hi Sam - I’ve tried emailing you regarding membership of your website. Haven’t heard back. Do you have a direct email? Thanks
Hi Tony, check out membership to gain access to all resources provided by Enterprise DNA - enterprisedna.co/membership
Hi Sam, I’ve seen this already, thanks. I have a number of questions regarding the membership, suitability for my requirements, before committing to the purchase. Do you have a contact email? Many thanks
In ideal world you got such a data. Wish I can implement it as it so. Reality is most of the time totally different and you got data all over the places and budget, haha, definitely not like that. But good video as usual.