How to use DATESBETWEEN function to calculate the Sum of Sales between two Dates in Power BI | Power BI Blog

The DAX Function DATESBETWEEN to calculate Sum of Sales between two Dates in Power BI
Scenario :

We have a FactSales table which has the sales data for 3 Consecutive Years like 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]),


Next create a one more Measure same as above, using the below DAX Formula

GrossSales_2016H2 = CALCULATE(SUM(FactSales[Gross_Sales]),

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

2Next View the Data in a Tabular Report using the new Measures and a Field “Cust_Name” from the Related Table “DimCustomers

 3Create 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 & Analytics Professional


Source link

Be the first to comment

Leave a Reply

Your email address will not be published.