Scenario:
When creating Power BI reports, a very common requirement is to be able to apply filters for a certain day, month, quarter or year. Here I will show a method on how to create a report to allow us to dynamically display data at any date granularity required.
Sample Data:
This is a sheet with three years of sales data.
Operations:
1.Create a custom calendar table
Calendar Date =
ADDCOLUMNS (
CALENDAR ( MIN ( Sales[Date] ), MAX ( Sales[Date] ) ),
"Year", YEAR ( [Date] ),
"YearQuarter", FORMAT ( [Date], "YYYY/\Qq" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearDay", FORMAT ( [Date], "yyyy/mm/dd" )
)
To facilitate the sorting of each date granularity in subsequent steps, we need to create the following sort columns for year, quarter, month and day.
No.Year =RANKX ( 'Calendar Date', 'Calendar Date'[Year],, ASC, DENSE )
No.Quarter = RANKX('Calendar Date','Calendar Date'[YearQuarter],,ASC,Dense)
No.Month = RANKX('Calendar Date','Calendar Date'[YearMonthnumber],,ASC,Dense)
No.Day = RANKX('Calendar Date','Calendar Date'[Date],,ASC,Dense)
Calendar Date Table :
2.Create a date granularity table
Create granularity tables of year, quarter, month and day based on calendar table. Then merge them using the UNION function
Date granularity table =
UNION (
SELECTCOLUMNS (
'Calendar Date',
"Date", 'Calendar Date'[Date],
"Date granularity", 'Calendar Date'[Year],
"No.Date granularity", 'Calendar Date'[No.Year],
"Type", "Year",
"No.Type", 4
),
SELECTCOLUMNS (
'Calendar Date',
"Date", 'Calendar Date'[Date],
"Date granularity", 'Calendar Date'[YearQuarter],
"No.Date granularity", 'Calendar Date'[No.Quarter],
"Type", "Quarter",
"No.Type", 3
),
SELECTCOLUMNS (
'Calendar Date',
"Date", 'Calendar Date'[Date],
"Date granularity", 'Calendar Date'[YearMonthnumber],
"No.Date granularity", 'Calendar Date'[No.Month],
"Type", "Month",
"No.Type", 2
),
SELECTCOLUMNS (
'Calendar Date',
"Date", 'Calendar Date'[Date],
"Date granularity", 'Calendar Date'[YearDay],
"No.Date granularity",'Calendar Date'[No.Day],
"Type", "Day",
"No.Type", 1
)
)
Date granularity table:
- Create the measure of the chart value
You need to set a “what if parameter” to easily get dynamic parameter.
Then create a measure to calculate sales as follows:
N sale =
var N='Parameter N'[Parameter N Value] //Dynamic parameter n
var slicerdate=MAX('Date granularity table_Slicer'[No.Date granularity])
return
if(
AND(
MAX('Date granularity table'[No.Date granularity])<=slicerdate,
MAX('Date granularity table'[No.Date granularity])>slicerdate-N
),
CALCULATE([Measure_sales],TREATAS(VALUES('Date granularity table_Slicer'[Type]),'Date granularity table'[Type])))
Tips:Here we use TREATAS function.It applies the result of a table expression as filters to columns from an unrelated table.
4.Create charts and slicers
1) Use the field ‘Date granularity table ‘ [date granularity] to generate a line chart.
2) Copy “date granularity table” as a new table ‘Date granularity table_Slicer’ to avoid cross-filtering.
3) Use the field ‘Date granularity table_Slicer ‘ [date granularity] to create the first slicer and field ‘Date granularity table_Slicer ‘ [Type] to create the second slicer.
4) Select the first slicer to make it active, and use edit interactions option to disable the interaction with the second slicer.
5) Sort the slicer and chart by “Date granularity”
5.Create dynamic title
Apply the following measure to “Title text” in visual fomat pane to achive title dynamic effect.
Dax(measure):
1)
Dynamic title_line_chart = "Sales by recent "&'Parameter N'[Parameter N Value]&" "&SELECTEDVALUE('Date granularity table_Slicer'[Type]) &"(s)"
2)
Dynamic title_slicer = "latest "& SELECTEDVALUE('Date granularity table_Slicer'[Type])&" Setting"
Finial result:
The above is the whole process of how to dynamically display the last data based on date granularity . You can check my sample file for more details. Hope it can help someone with similar needs.
Author: Eason Fang
Reviewer: Kerry & Ula
Be the first to comment