Now, here is the monthly quota working perfectly: Var whichQ=LASTDATE(DimDate)ĬALCULATE(SUM(FactSalesQuota),firstdateofQ)/3 Finally, I wrapped it all inside a CALCULATE function to get the SalesQuota for the first of the quarter and then divided it by three to get the monthly Sales Quota. I used LASTDATE function to find out the current date in the visual’s row, and then STARTOFQUARTER function to fetch the start of that quarter. and then get the SalesQuota of that date. So, your job, in the DAX calculation, is to get the first quarter of each month (if your budget data is monthly, then you should get the first of each month instead). Let’s go through an example: for August 2005: there is no SalesQuota for this month because the SalesQuota is for the 1st of July 2005. The issue is that in a given month, there might be no SalesQuota. You should use some of the DAX techniques to overcome the issue. However, the problem is that when you go to the monthly level of slicing and dicing, then for some months, there is no value! The monthly calculation seems to be simple, each quarter will be three months in total, so the calculation of that would be the quarterly value divided by three. And that is what we are going to explore in this article. However, sometimes, the requirement is, to calculate the budget split of the quarterly budget into monthly, weekly, or even daily. Because there is no monthly budget, so the monthly analysis of that doesn’t make sense. The important note here is that the budget data is only valid down to quarter level. actual would be wrong in every month, for the first month, the budget value is so high (because it is for 3 months), and for the other two months is blank. In a monthly analysis of the data, the FactResellerSales has values for each month, but because the other table doesn’t, then the calculation of budget vs. However, if you start analyzing data on a lower grain, for example, Month, you get something like this:Īs you can see the FactSalesQuota only have values in the first month of each quarter. The schema above works perfectly if you slice and dice data by the grain supported by the budget table (or let’s say FactSalesQuota table) which is Quarter in this example. Here is how the data in the Sales Quota table looks like: (FactSalesQuota in the AdventureWorksDW) To connect both tables to the same Date dimension, we considered the first day of every quarter as the Date field for the FactSalesQuota, and then connected them through a star schema approach. FactSalesQuota which is on a grain of every QUARTER, and Employee.FactResellerSales which is on a grain of every DAY, every product, SalesTerritory, and Employee.In the data model above, we have two fact tables The model that we build for this sample in the previous article is as below: The sample dataset used here is the AdventureWorksDW Excel file which can be downloaded from here. Actual: Zero Complexity model in Power BI Build Your First Star Schema Model in Action.Creating a Shared Dimension Using Power Query and Power BI.Combining Dimension Tables in Power BI using Power Query Foundation of Modeling in Power BI.Basics of Modeling in Power BI: Fact Tables.What is a Dimension table and why say No to a single big table.Data preparation First and Foremost Important task.What is the Direction of the Relationship?.What is the Cardinality of the Relationship?. However, I highly recommend you to read below articles beforehand There is no prerequisite for this article. Actual Zero Complexity data model in Power BI. I explained in this article, how to build a Budget vs. To learn more about Power BI, read Power BI book from Rookie to Rock Star. In this article, I’m going to explain some calculations using DAX that helps to go to a lower grain that what the fact table supports. However, if you want to go to lower grain than what the table supports, then you would need more calculations, and DAX can handle that easily. As long as you slice and dice data in the level of granularity that both tables support, then you don’t need anything else. The model works perfectly as a star schema. actual, where the grain of the two fact tables is different. In the previous article, I explained how you can build a data model for budget vs.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |