Thursday, 14 December 2017

DAX Formula to Calculate Cumulative Sum for Quarterly and Monthly in Power BI

How to Create a New Measure to Calculate the Cumulative Sum for QTD and MTD in Power BI
Scenario :
Suppose we have a FactSales Tables follows..




On this Table, I have created two new Calculated Measures "QTD_GrossSales" and "MTD_GrossSales" as below, to calculate the Cumulative Total by Quarter and Month.

QTD Cumulative Sum :
QTD_GrossSales = CALCULATE(SUM(FactSales[Gross_Sales]),
DATESQTD(FactSales[Order_Date]))


Result :


MTD Cumulative Sum :
MTD_GrossSales = CALCULATE(SUM(FactSales[Gross_Sales]),
DATESMTD(FactSales[Order_Date]))

Result :

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

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
--------------------------------------------------------------------------------------------------------

Tuesday, 12 December 2017

How to Create a New Calculated Table from Existing Table in Power BI

Creating a New Calculated Table from Existing Table in Power BI
We can Create a New Table with Single Column using the "DISTINCT" Function on Existing Table. Also, we can create a New Table with Multiple Columns using the "SUMMARIZE" Function on Existing Related Tables in Power BI.

1)Creating a New Table with Single Column from One Existing Table:
Suppose we have a Table "vSalesByCustRegion" with Multiple Fields as follows.



From this Table, we wants to extract the Distinct Product Names into a New Table called "Tbl_DistProdNames".

A)Go to Power BI Report > Modeling Tab > New Table
Click on the New Table 



B)Next Enter the DAX Expression/Formula to Create the Table
Enter the below DAX Formula in the Formula Bar

Tbl_DistProdNames = DISTINCT(vSalesByCustRegion[Prod_Name])



Next press Enter Key , it will Create a New Table Tbl_DistProdNames with One Field "Prod_Name"

Result :



2)Creating a New Table with Multiple Columns from One Existing Table :
Here want to Create a New Table "Tbl_CustDetails" with Multiple Columns from an Existing Table.

Enter the below DAX Formula in the Formula Bar


Tbl_CustDetails = SUMMARIZE(vSalesByCustRegion,vSalesByCustRegion[Cust_Name],vSalesByCustRegion[Cust_Segment],vSalesByCustRegion[Region_Name])

Result :

3)Creating a New Table with Multiple Columns from Multiple Related Tables :

Here want to Create a New Table "Tbl_CustProdSup" with Multiple Columns from an Existing Related Table.

Please note when we are creating a New Table from Multiple Tables, there should be a defined relation between them.

Here in my Data Model, I have one Fact Table and 3 Dimension Tables , which have already a Relation between them.


When we are Creating a New Table using Summarize Formula , Once we enter the Summarization Table Name (here the FactSales Table ) it will show the Related Table Names and their Fields that we can use for Group By.


Enter the below DAX Formula in the Formula Bar

Tbl_CustProdSup= SUMMARIZE(FactSales,DimCustomers[CustName],DimProducts[Prod_Name],DimSuppliers[Supplier_Name])

Result :


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

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts