Wednesday, 13 December 2017

How to Create a New Measure to Calculate the Sum of Sales Between Two Dates in Power BI

DAX Formula to Create a New Calculated Measure to Find the Sum of Sales Between Two Dates in Power BI
Scenario :
We have a FactSales table which has the sales data for 3 Consecutive Years (2014,2015,2016).

FactSales Table :
From this Data, Client interested in analyzing FY16-H1(First 6 Months of FY16) against FY16-H2 (Next 6 Months of FY16), to see the Sales Performance.

To fulfil this Scenario we will create the New Measures and a Line Chart (Dashboard) as follows..

1) Creating the New Calculated Measures using DAX Formulas
Here we will Create two new Calculated Measures based on the FactSales Table

A) Go to Modeling Tab > New Measure
Select the Table in which we want Create the Measures, and then Click on New Measure from Modeling Tab.


B) Next Create Measures "GrossSales_2016H1" and "GrossSales_2016H2"
Here we will create two Measures one to calculate the Sum(Gross_Sales) for first 6 Months 
and another one for Next 6 Months of FY16, using the DAX Formulas.



Enter the below DAX Formula in Formula bar the Press Enter.
GrossSales_2016H1 = CALCULATE(SUM(FactSales[Gross_Sales]),

DATESBETWEEN(FactSales[Order_Date],
DATE(2016,01,1),
DATE(2016,06,30)
))



Next create a one more Measure same as above, using the below DAX Formula
GrossSales_2016H2 = CALCULATE(SUM(FactSales[Gross_Sales]),
DATESBETWEEN(FactSales[Order_Date],
DATE(2016,6,1),
DATE(2016,12,31)
))

Now 2 new Measures have been Created in the SalesFact Table as follows..

2) Next View the Data in a Tabular Report using the new Measures and a Field "Cust_Name" from the Related Table "DimCustomers"
 3) Create a Line Chart to Visualize the Sales Performance of FY16-H1 vs. FY16-H2

Note :

To Format the Chart, first Select the Chart, and then go to the Format and work around various Formatting options available for various Chart elements.



--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts