Scenario:
Suppose we have a Date Model as follows:
The Relations are as follows:
Now from the above Model, we need to calculate the Monthly Sales comparison between the Current Month and Previous Month with below requirement:
1) If there is No selection on Year and Month, then we need to show the Sales of the Current in the Current Year.
2) If there is a selection on Year and Month, then we need to show the Sales of the selected Year or Month.
————————————————————————————
To fulfill the above requirement, we need to create the following Calculated Columns on the Date Table (tbl_Calendar) as discussed below:
The below DateFilter Flag will be helpful to filter out extra dates that flowing from tbl_Calendar table, for which no Sales in the fact table:
DateFilterFlag =
VAR vMinFlag = tbl_Calendar[cDate]>=MIN(tbl_OrderDetails[Order_Date])
VAR vMaxFlag = tbl_Calendar[cDate]<=MAX(tbl_OrderDetails[Order_Date])
RETURN
IF( AND(vMinFlag=TRUE(), vMaxFlag=TRUE()), 1,0)
The below Current Month Flag will be helpful to filter the Current Month in Current Year while doing the calculations:
Cur Month Flag =
IF(
YEAR(tbl_Calendar[cDate])=YEAR(TODAY())&&
MONTH(tbl_Calendar[cDate])=MONTH(TODAY()),
1,0)
The below Current Year Flag will be helpful to filter the Current Year while doing calculations:
Cur Year Flag = IF( MONTH(tbl_Calendar[cDate])=YEAR(TODAY()),1,0)
————————————————————————————
To fulfill the above requirement, we need to create the following Measures on the above Model as discussed below:
Net Sales =
SUMX ( tbl_Sales, ( tbl_Sales[Unit_Price] * tbl_Sales[Units_Sold] ))
————————————————————————————
The dynamic Measures to show the sales of Current Month, Previous Month, and Delta of the Current Month vs Previous Month, which will be shown on the Card visuals:
Current Month Sales =
VAR vNetSales = [Net Sales]
VAR vFilterFlag = OR(ISFILTERED(tbl_Calendar[Year]),
ISFILTERED(tbl_Calendar[Month_Name])
)
VAR vCurMonthSales =
CALCULATE(
[Net Sales],
tbl_Calendar[Cur Month Flag]=1)
RETURN
IF( vFilterFlag=TRUE(),
vNetSales, vCurMonthSales
)
Previous Month Sales =
VAR vFilterFlag = OR(ISFILTERED(tbl_Calendar[Year]),
ISFILTERED(tbl_Calendar[Month_Name])
)
VAR vPrevSales =
CALCULATE (
[Net Sales],
DATEADD( tbl_Calendar[cDate], –1, MONTH )
)
VAR vPrevMonthSales =
CALCULATE(
[Net Sales],
DATEADD(CALCULATETABLE(DISTINCT(tbl_Calendar[cDate]),
tbl_Calendar[Cur Month Flag]=1),-1, MONTH)
)
RETURN
IF( vFilterFlag=TRUE(),
vPrevSales, vPrevMonthSales
)
Cur Month vs Prev Month =
[Current Month Sales]-[Previous Month Sales]
% Cur Month vs Prev Month =
DIVIDE(([Current Month Sales]-[Previous Month Sales]),
[Previous Month Sales])
————————————————————————————
The Measures to show the sales of Current Month, Previous Month, and Delta of the Current Month vs Previous Month, which will be shown on the Table visual:
The below Measures will show the sales of Current Month, Previous Month, and Delta of the Current Month vs Previous Month, when the Month field is in the Filter Context of the Visual.
Cur Month Sales =
CALCULATE([Net Sales], tbl_OrderDetails[Order_Date]<>BLANK())
Prev Month Sales =
CALCULATE (
[Cur Month Sales],
DATEADD( ‘tbl_Calendar'[cDate], –1, MONTH )
)
Var of Monthly Sales =
[Cur Month Sales]-[Prev Month Sales]
% Var of Monthly Sales =
VAR vRatio = DIVIDE(([Cur Month Sales]-[Prev Month Sales]),
[Prev Month Sales],BLANK()
)
RETURN
SWITCH(TRUE(),
ISBLANK([Cur Month Sales]), BLANK(),
ISBLANK([Prev Month Sales]), 1,
CALCULATE(vRatio, tbl_OrderDetails[Order_Date]<> BLANK())
————————————————————————————
Now we will see the result of all the above formulas as follows.
Case 1: Default view with no selection on Year and Month Slicers/Filters:
Notes:
In the above we have enabled the Rules for Growth Indicators for the [% Var of Monthly Sales] as per below:
We can also create a Custom Icons using the UNICHAR() Function as showing below:
Growth Icon =
SWITCH ( TRUE(),
[% Cur Month vs Prev Month]=BLANK(), UNICHAR(9632),
[% Cur Month vs Prev Month]=1.00, UNICHAR(9632),
[% Cur Month vs Prev Month]<0, UNICHAR (9660),
[% Cur Month vs Prev Month]>0, UNICHAR (9650)
)
Based on the above Icons, we can apply the Color for each Icon using the below logic:
Color Icon =
SWITCH ( [Growth Icon],
UNICHAR(9632), “#666666”, //Grey
UNICHAR(9660),“#FD7F6F”, //Red
UNICHAR(9650), “#87BC4F” //Green
)
Result:
Finally we will build a Combo Chart ( Line and Clustered Column Chart) as follows:
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment