Scenario:
Suppose we have a Data model as follows.
The relationships are as per below:
Based on this Model, we need to show the Sales Growth for the selected dynamic period of Months.
Ex:
Sales Growth between the Dynamic Previous Month and Current Month ( May-2022 and August-2023)
Sales Growth between the Dynamic Previous Month and Current Month ( May-2022 and August-2023)
Expected Result:
Say Aug-2022 is the Current Month (Last/End Data Point) and May-2022 is the Previous Dynamic Month (Frist Data Point)
The Sales Growth between these two Data Points needs to display with use of Error Bars and a Growth label.
We can achieve this Scenario by the following way.
Step 1: Create a Numeric Range Field Parameter:
We will use this Numeric Range Parameter to choose the Dynamic Previous Months from the Current Month (Selected Month).
PrevMonthsPeriod = GENERATESERIES(1, 12, 1)
Prev Month Range Value = SELECTEDVALUE('PrevMonthsPeriod'[Prev Month Range])
----------------------------------------------------------------------------
Step 2: Create a disconnected (from Model) Date table for a Month Slicer:
We will create a Date table for a Month Slicer using the below DAX. This table is disconnected table, which means it has not relationship in Data Model
We will create a Date table for a Month Slicer using the below DAX. This table is disconnected table, which means it has not relationship in Data Model
SlicerCalendar =
VAR vCalTable = CALENDAR(DATE(2022,01,01), DATE(2023,12,31))
VAR vMyCal =
ADDCOLUMNS(
vCalTable,
"Month_Year", FORMAT([Date],"mmm-yyyy"),
"SortKey", YEAR([Date]) & UNICHAR(MONTH([Date]) + 64)
)
RETURN
vMyCal
Note:
The SortKey is used to Sort the Month_Year. We can use Month_Year as Slicer to dynamically choose the Current Month.
Step 3: Create the required Measures:
We need to create the following required measures to implement our Scenario.
Total Sales = SUMX(tbl_Sales , tbl_Sales[Unit_Price]*tbl_Sales[Units_Sold])
Moving Period Sales Points =
VAR CurrentMonth = EOMONTH(MAX(SlicerCalendar[Date]), 0)
VAR Period = [Prev Month Range Value]
VAR DateRange =
DATESBETWEEN(SlicerCalendar[Date],
EOMONTH(CurrentMonth, -Period) + 1, CurrentMonth
)
VAR FilteredCalendar =
CALCULATETABLE(
DimCalendar,
TREATAS(DateRange,DimCalendar[DimDate])
)
VAR RangeStartMonthDates =
DATESBETWEEN(DimCalendar[DimDate], EOMONTH(CurrentMonth, -Period) + 1,
EOMONTH(EOMONTH(CurrentMonth, -Period) + 1,0)
)
VAR RangeEndMonthDates =
DATESBETWEEN(DimCalendar[DimDate],
EOMONTH(CurrentMonth, -1) + 1, CurrentMonth
)
VAR UnionStartEndMonthDates = UNION(RangeStartMonthDates, RangeEndMonthDates)
VAR MaxSales =
CALCULATE(
MAXX(
ALLSELECTED(DimCalendar[MonthName]),
[Total Sales]
),
FilteredCalendar
)
VAR SalesInDateRange =
CALCULATE(
[Total Sales],
UnionStartEndMonthDates,
KEEPFILTERS(DimCalendar)
)
RETURN
IF(SalesInDateRange, MaxSales + 100000)
----------------------------------------------------------------------------
The following measure is used for the Error Bars that connects the Start and End data points.
Moving Period Sales Line =
VAR CurrentMonth = EOMONTH(MAX(SlicerCalendar[Date]),0)
VAR Period = [Prev Month Range Value]
VAR DateRange =
DATESBETWEEN(SlicerCalendar[Date],
EOMONTH(CurrentMonth, -Period)+ 1, CurrentMonth
)
VAR FilteredCalendar =
CALCULATETABLE(
DimCalendar,
TREATAS(DateRange,DimCalendar[DimDate])
)
VAR MaxSales =
CALCULATE(
MAXX(
ALLSELECTED(DimCalendar[MonthName]),
[Total Sales]
),
FilteredCalendar
)
VAR SalesInDateRange =
CALCULATE(
[Total Sales],
FilteredCalendar,
KEEPFILTERS(DimCalendar)
)
RETURN
IF(SalesInDateRange, MaxSales + 100000)
----------------------------------------------------------------------------
The following measure will use the above Measure.
Moving Period Data Points = IF([Moving Period Sales Points], [Total Sales])
----------------------------------------------------------------------------
The following measure is used to calculate the Growth based on Start and End data points and display on the Error Bars.Sales Growth Label =
VAR CurrentMonth = EOMONTH(MAX(SlicerCalendar[Date]),0)
VAR Period = [Prev Month Range Value]
VAR DateRange =
DATESBETWEEN(SlicerCalendar[Date],
EOMONTH(CurrentMonth, -Period)+ 1, CurrentMonth
)
VAR RangeStartMonthDates =
DATESBETWEEN(DimCalendar[DimDate], EOMONTH(CurrentMonth, -Period) + 1,
EOMONTH(EOMONTH(CurrentMonth, -Period) + 1,0)
)
VAR RangeEndMonthDates =
DATESBETWEEN(DimCalendar[DimDate],
EOMONTH(CurrentMonth, -1) + 1, CurrentMonth
)
VAR StartPointSales =
CALCULATE(
[Total Sales],
RangeStartMonthDates
)
VAR EndPointSales =
CALCULATE(
[Total Sales],
RangeEndMonthDates
)
VAR LastPointSales =
CALCULATE(
[Total Sales],
RangeEndMonthDates,
KEEPFILTERS(DimCalendar)
)
VAR Diff = DIVIDE(EndPointSales, StartPointSales) - 1
RETURN
IF(LastPointSales, FORMAT(Diff, "0.0 % Growth; (0.0 %) Drop"), " ")
----------------------------------------------------------------------------
The following measure is used to format the Growth label value with Red or Green.
Format Growth Label =
VAR CurrentMonth = EOMONTH(MAX(SlicerCalendar[Date]),0)
VAR Period = [Prev Month Range Value]
VAR DateRange =
DATESBETWEEN(SlicerCalendar[Date],
EOMONTH(CurrentMonth, -Period)+ 1, CurrentMonth
)
VAR RangeStartMonthDates =
DATESBETWEEN(DimCalendar[DimDate], EOMONTH(CurrentMonth, -Period) + 1,
EOMONTH(EOMONTH(CurrentMonth, -Period) + 1,0)
)
VAR RangeEndMonthDates =
DATESBETWEEN(DimCalendar[DimDate],
EOMONTH(CurrentMonth, -1) + 1, CurrentMonth
)
VAR StartPointSales =
CALCULATE(
[Total Sales],
RangeStartMonthDates
)
VAR EndPointSales =
CALCULATE(
[Total Sales],
RangeEndMonthDates
)
VAR LastPointSales =
CALCULATE(
[Total Sales],
RangeEndMonthDates,
KEEPFILTERS(DimCalendar)
)
VAR Diff = DIVIDE(EndPointSales, StartPointSales) - 1
RETURN
IF(LastPointSales, IF(Diff < 0, "red", "green"))
----------------------------------------------------------------------------
Step 4: Create a Line Chart with use of above Measures:
We can use the above Measures on the Y-Axis, and Month-Year on X-Axis as shown below.Please note that the Month-Year, should take from the Date table (DimCalendar) used in the Data Model, instead of disconnected SlicerCalendar table.
Next, apply settings for the Error bars as per below:
Choose the Moving Period Sales Points as Upper bound for the series Total Sales.
Next, apply settings for the Data Labels as per below:
For the Moving Period Sales Line, under value section of the Data Labels, use the field Sales Growth Label.
Notes:
Please note, the above logics consider the Selected Month(Aug-2023) as the End Point and from that point to Previous 4th Month(May-2023) will be considered as a Start Point.
Next, apply settings for the Error bars as per below:
Choose the Moving Period Sales Points as Upper bound for the series Total Sales.
Next, apply settings for the Data Labels as per below:
Turn off the Data Labels for Total Sales but enable for the Moving Period Sales Line (Data Bars) and Moving Period Sales Points.
For the Color format of the Growth Label, use the measure Format Growth Label.
Next apply settings for the Markers as per below:
Turn off the Markers for Moving Period Sales Line but enable for the Total Sales and Moving Period Data Points.
Finally, the Result is as follows:
Notes:
Please note, the above logics consider the Selected Month(Aug-2023) as the End Point and from that point to Previous 4th Month(May-2023) will be considered as a Start Point.
You can customize the above logics and apply for your own Scenario.
Happy Learning and Keep Growing.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.