Purpose: Create a dashboard to present product year-over-year demand by month in MS Power BI.
Tool:
— Dashboard: MS Power BI
— Backend DB: MS SQL Server
Steps:
1. Import data from [Simulation].[005_Demand_By_Product_Month] stored in the server into Power BI called Query1.
2. In Power BI, we create a line chart to present YOY demand by month. Also, In the top right area, a summary present demand in unit for 2018 and 2019.
3. Some of you may realize that the numbers for 2018 and 2019 are whole-year data (For 2019, it is year-to-date demand in unit). They are not comparable because the underlying data for 2019 only consists of 10 months. As a result, how to create a dynamic measure to present last-year data with the months we have this year (In this case, we will only need to present Jan to Oct for 2018)?
4. First of all, we are going to create a measure called YYYYMM_Max to get the maximum value of YYYYMM this year, which is 201910.
YYYYMM_Max = CALCULATE ( MAX(Query1[YYYYMM]), ALLSELECTED(Query1) )5. Second, calculate the largest month of last year.
YYYYMM_Max_LY = [YYYYMM_Max] - 1006. Finally, create a measure called Units_LYTD to aggregate the demand in unit till 2018 Oct.
Units_LYTD = CALCULATE ( SUM(Query1[Units]), FILTER ((Query1), Query1[YYYYMM] <= [YYYYMM_Max_LY] ) )Thanks! Enjoy it :)
