Friday, 22 March 2019

How to get the Previous Row value from a Column in Power BI

How to return Previous Row value from a Column in Power BI
Scenario:
Suppose we have a Same data as follows..
from this data, column "TotalUnits", we wants generate new calculated column to show the same TotalUnits but the each row value should get from the Previous Row value.
This can be in one of the way, by creating a Index Column on this Table.
The Index Column can be created from Home> Edit Queries > Add Column > Index Column

Next based on this Index Column, we will create a new Calculated Column "PrevRowUnits",
that shows the TotalUnits values that get from the Previous Rows, using the below DAX Expression :
PrevRowUnits = LOOKUPVALUE('Sample'[TotalUnits],'Sample'[Index],'Sample'[Index]-1)+0

Finally the Result is as follows :

Note:
The above Scenario may not be a good, but you can relate it to your own Scenario.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

Saturday, 16 March 2019

How to calculate Previous Weeks Actual based on Current Week Selection in Power BI

How to Calculate Previous Weeks data based on Slicer Selection of Current Week in Power BI
Scenario :
Suppose we have a data Model as follows..

Based on this Model, We can develop a Report that shows the data for Previous 3 Weeks based on the Current Week , selected from the WeekNum slicer, as shown below:

Note :
In my report, I have used the less data so that we are seeing most of the Blank/null values.

Slicers Used :
In the Report we have used the below 3 Slicers, from related Calendar table :
FiscalQtr : It will have the Fiscal Quarter values (eg: FY14Q1,FY14Q2..etc)
MonthName : It will have respective Months of the respective  Filtered Quarter Context.
WeekNum : It usually contains Week Numbers like 1,2,3....51,52,53., shows the respective Week Numbers based on the Filtered context.

Measures used for Cards Labels:
In the Report we have used the Cards to show 4 Weeks numbers as Labels defined using the below DAX Expressions.
Cur Week = CALCULATE(SELECTEDVALUE('Calendar'[WeekNum]))
Prev Week1 =
[Cur Week]-1
Prev Week2 =
[Cur Week]-2
Prev Week2 =
[Cur Week]-3
Note :
The card 'Cur Week' is the measure used for storing the Current Week number which we selected from the Slicer WeekNum.

Measures used for calculating Weekly Actual :
TotalSales = CALCULATE(SUMX(FactSales, FactSales[Units_Sold]*RELATED(DimProducts[Unit_Price])))

Cur Wk Act = IF(ISBLANK(CALCULATE([TotalSales],
FILTER(ALL('Calendar'),'Calendar'[WeekNum]=SELECTEDVALUE('Calendar'[WeekNum])))),
0, CALCULATE([TotalSales],
FILTER(ALL('Calendar'),'Calendar'[WeekNum]=SELECTEDVALUE('Calendar'[WeekNum]))))


Prev Wk1 Act = 
VAR CurWeek=SELECTEDVALUE('Calendar'[WeekNum])
VAR CurYear=SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNum=CALCULATE(MAX('Calendar'[WeekNum]), ALL('Calendar'))
RETURN
CALCULATE([TotalSales],FILTER ( ALL('Calendar'),
                  IF(CurWeek=1,
                       'Calendar'[WeekNum]=MaxWeekNum && 'Calendar'[Year]=CurYear-1,
                       'Calendar'[WeekNum]=CurWeek-1 && 'Calendar'[Year]=CurYear
                      )))

Prev Wk2 Act = 
VAR CurWeek=SELECTEDVALUE('Calendar'[WeekNum])
VAR CurYear=SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNum=CALCULATE(MAX('Calendar'[WeekNum]), ALL('Calendar'))
RETURN
CALCULATE([TotalSales],FILTER ( ALL('Calendar'),
                  IF(CurWeek=1,
                       'Calendar'[WeekNum]=MaxWeekNum && 'Calendar'[Year]=CurYear-1,
                       'Calendar'[WeekNum]=CurWeek-2 && 'Calendar'[Year]=CurYear
                      )))

Prev Wk3 Act = 
VAR CurWeek=SELECTEDVALUE('Calendar'[WeekNum])
VAR CurYear=SELECTEDVALUE('Calendar'[Year])
VAR MaxWeekNum=CALCULATE(MAX('Calendar'[WeekNum]), ALL('Calendar'))
RETURN
CALCULATE([TotalSales],FILTER ( ALL('Calendar'),
                  IF(CurWeek=1,
                       'Calendar'[WeekNum]=MaxWeekNum && 'Calendar'[Year]=CurYear-1,
                       'Calendar'[WeekNum]=CurWeek-3 && 'Calendar'[Year]=CurYear
                      )))

Notes :
The Measure 'Cur Wk Act' will calculates the Actuals/Bookings of the Week that we selected in the Slicer WeekNum.
In this Measure we have used the IF(ISNULL()) Functions to replace the Nulls by Zeros.
In other Measures we have not used the IF(ISNULL()) Functions, hence those columns showing the Blank values.
In all the above Measures we considered [Year], to make sure the Previous Week will be calculated correctly when we are in Week1 (eg: FY13WK53 vs FY14WK1).

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

Sunday, 10 March 2019

How to Filter data dynamically using Time Intelligence Selector

How to dynamically view YTD, QTD and MTD data using Time Intelligence Selector 
Scenario :
Suppose we have the data Model in our Power BI Report as follows..

We have the "SelectMeasure" table with data as follows..

Next in this Table, we will create a new Measure using the below DAX Expression :
SelectorNum = MIN(SelectMeasure[SelectorNumber])

Next we create a Slicer (Horizontal Orientation) on the report using the above table. This will be used as a dynamic Query Filter for viewing the data.
Next we create the required Measures using the below DAX Expressions :
SumSales = SUM(FactSales[Net_Sales])
ActualMTD = CALCULATE([SumSales], DATESMTD(FactSales[Order_Date]))
ActualQTD
= CALCULATE([SumSales], DATESQTD(FactSales[Order_Date]))
ActualYTD
= CALCULATE([SumSales], DATESYTD(FactSales[Order_Date]))

Notes :
     The "[SumSales] is the Total Sum of Sales.
     The other Actual measures will be created based on the measure [SumSales].
     [ActualMTD] - returns the SumSales of the Current Month(eg : FY16Q4M3).
     [ActualQTD] - returns the SumSales of the Current Quarter(eg: FY16Q4).
     [ActualYTD] - returns the SumSales of the Current Year(eg: FY16)


Next we will create a new Measure [ActualSelect], using the Switch Function which will be selecting the values from the above Actual Measures based on our Selector (i.e., Slicer)
ActualSelect = SWITCH([SelectorNum]
  , 1, [ActualMTD]
  , 2, [ActualQTD]
  , 3, [ActualYTD]
)

Now we can use the measure [ActualSelect], in the report, to dynamically Filter the Data in the the Report based on the Slicer/Selector items as follows..

Notes :
In the above Card in blue color, the label ( FY16 Sales : 219 M) is customized using the below DAX Expression :
SumLabel = "FY"&RIGHT(MAXX(FactSales,YEAR(FactSales[Order_Date])),2)&" Sales : "& FORMAT([SumSales]/100000,"0 M")

Suppose if we have a Scenario (not related to above Article) where we have the data with Actuals and Forecast, in that case, we can calculate the Actual/Forecast using the below similar DAX Functions :
Actual = CALCULATE([SumAmount], Scenario[ScenarioName] = "Actual")
Forecast = CALCULATE([SumAmount], Scenario[ScenarioName] = "Forecast")
Actual/Forecast = SUMX(VALUES(FactSales[FiscalYear]),IF(ISBLANK([Forecast]), [Actual], [Forecast]))

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

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts