How to set TODAY as default slicer selection dynamically.
How to set TODAY as default slicer selection dynamically.
(Responsive power bi report included at the end of this post.)
Requirement: set today as default slicer selection dynamically. (whenever the report is opened that date should be the default value)
source: excel, containing a table named "sales" with 2 columns past, present, future dates as a date column, and sales target values in another column.
algorithm
1)creating a dimdate table with the date values in the sales table
2) creating a new column with condition (equating date with today and naming it as "today", and the dated lesser than today(current date) shod be displayed as same dated)
3)removing the blank values
Procedure Explanation :
creating dimdate table.
DIMDATE = CALENDAR(MIN(SALES[Date]),MAX(SALES[Date]))
creating a column named "slicer date" in dimdate table for slicer selection.
slicer date = IF(DIMDATE[Date]=TODAY(),"Today",IF(DIMDATE[Date]<TODAY(),DIMDATE[Date]&""))
Here in the above dax we used nested if functions to get the desired requirement.
first if is used to find if date is equal to today/current day then return "today" text value
else go for second if function, here if date id less than today /current date then return the same date.
no else statement in second if function . the second if function returns dates.. but we cant have two data types in a column
(text "today" form first if fn. and date type from second if function) so to overcome this issue we converted the date type
in 2 nd if function to text type by assing a text value <&""> to it.
slicer date = IF(DIMDATE[Date]=TODAY(),"Today",IF(DIMDATE[Date]<TODAY(),DIMDATE[Date]&""))
Give the relationship between the dimdate and sales tables using date columns.
Take this slicer date column in the slicer and the dimdate[date] and total sales measure in table visual.
to ignore the blank rows in the slicer we will set the advanced option to "in not blank" in the visual level filter
set the default order of the slicer to descending order to get the today on top and rest below in descending order.
as of today's date while this post is made id 14 September 2021 we will get the output like below.
Method 1:To make it default save the file by selecting the today option in the slicer and publish it.
Method 2: write dax to make today as default dynamically.
write a measure to set default date to today dynamically.
Default slicer selection sales =
var selecteddate=SELECTEDVALUE(DIMDATE[slicer date])
var selected_date_sales=CALCULATE([TOT TARGET SALES],
DIMDATE[slicer date]=selecteddate)
var defalt_date_sales=CALCULATE([TOT TARGET SALES],DIMDATE[slicer date]="Today")
var result=IF(HASONEVALUE(DIMDATE[slicer date]),selected_date_sales,defalt_date_sales)
return result
then we need to sort this slicer date column with the date in the dimdate table..
but we can't see this option if we create the dimdate table with calendar auto function.
In that scenario please use the calendar function instead of the calendar auto function, save/enter, and then revert back to calendar auto, save/enter to get the sort by date column for this slicer sales column....
Note:Sometimes the below interactive Power BI report may be 5:30 min late to Indian standard time as the Pbi services server may be at UTC 0:00
Please have a look at the responsive power bi report below...
Method 1 :Default date selection by selecting manually (single selection) - Page 1 in report
Method 2 :Default date selection by DAX (Multi selection) - Page 2 in report
click on 
to view the responsive Power bi report
to view the responsive Power bi reportPlease follow and share
If you have any Questions/Suggestions please write in the comments below.
Thank you 🙏
P.M.Prasad
Comments
Post a Comment