How to Pass slicer date value to query start date and end date variables

[NehaSha]

Hi All,

I need help for the below mentioned scenario.

I have created the Dashboard report with below mentioned query which always update with by default parameter startdate and endate

Few columns need to calculate between startdate and enddate. Few columns are dependent only on end date for calculations.

Date field Automatically update by default for startdate and enddate update based on below mentioned query :

 --Default 1st of current month ‘4-1-2019’

DECLARE @START_DATE DATE

SET @START_DATE= cast(DATEADD(MONTH, DATEDIFF(MONTH, '19000101', Dateadd (D,Datediff(D,1,getdate()),0)) , '19000101')as date)

--Default yeasterday date ‘4-10-2019’

DECLARE @END_DATE DATE       SET @END_DATE= cast(Dateadd (D,Datediff(D,1,getdate()),0) as date)

;WITH CTE AS (SELECT H.[LocationID] AS LocationID,SUM([NetRent]) as [NetRent],SUM( h.Discounts) AS 'Discounts',SUM(h.FeesWaive ) AS 'FeesWaved',SUM(h.Merch_Sales) AS 'MerchSales' FROM #a as H

WHERE CAST(H.start_date AS DATE) BETWEEN @START_DATE AND @END_DATE            GROUP BY H.LocationID)

SELECT H.LocationID AS LocationID,M.DM AS 'DM' ,M.AreaTotal AS 'Total Area',M.[GrossPotRent] AS 'Gross Pot Rent' ,a.Discounts,a.[NetRent] ,a.[FeesWaved] as FeesWaved,a.[MerchSales] as MerchSales FROM cte a Left JOIN #Begining as M on a.[LocationID]=B.[LocationID]

WHERE CAST(M.start_date AS DATE) =@END_DATE

Each refresh automatic give the result based on default startdate and enddate

But --user need flexibility to change the date between start date and end date option on dashboard report as slicer.

 What could be the best way to pass date default value to slicer and slicer date value into the query ?

---I tried below mentioned steps but could nt reach anywhere

1. Created DIM date table from min and max of the data available.
2. Take Slicer on dashboard and pass value from dimdate table which shows min and max from dimdate dates between 2011-01-15 to 2019-04-10 –Need to set slicer default date values startdate (Default 1st of current month) and enddate (Default yeasterday date) ?
3. Created 2 Parameters startdate and enddate –manually pass 4-1-2019 and 4-10-2019 (but need to be pass slicer start and end date value to these Parameters don’t know how to do that) ?
4. Successfully able to Pass the Parameter value to the query which shows result.

If there can be another approach please suggest

Thanks for help in advance!

Thanks,

Neha Sharma

[Anonymous]

@NehaSha - You could add other attributes to your date table, such as Relative Month. You could use this as the slicer, or in addition to a date slicer. By default, you could select current month only. 

In the following M script example, the Bold sections are items that you would need to replace with your values.

    InsertRelativeMonth = Table.AddColumn(InsertRelativeQuarterDescription, "Relative Month", 
                                    each 12*([Year]-Date.Year(CurrentDate)) + ([Month Of Year]-Date.Month(CurrentDate))),
    InsertRelativeMonthDescription = Table.AddColumn(InsertRelativeMonth, "Relative Month Description", 
                                    each if [Relative Month] = 0 then "Current Month"
                                        else if [Relative Month] = -1 then "Last Month"
                                        else if [Relative Month] = 1 then "Next Month"
                                        else if [Relative Month] < -1 then Number.ToText(Number.Abs([Relative Month])) & " Months Back" 
                                        else Number.ToText([Relative Month]) & " Months Ahead"),

Cheers!

Nathan

[NehaSha]

Hi  ,

Thanks for your reply!

I am trying to understand the solution but didn’t really able to understand the steps :

You could add other attributes to your date table, such as Relative Month. You could use this as the slicer, or in addition to a date slicer. By default, you could select current month only.  --not sure how to do that 

Please see the below mentioned date table columns:

Calendar = ADDCOLUMNS (

CALENDAR (MIN(sitelink_site_summary_daily_history[start_date]),MAX(sitelink_site_summary_daily_history[start_date])),

"DateKey", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Month", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )

,"MMMYY", FORMAT([date],"MMM YY"),

"SortOrder",INT(CONCATENATE(YEAR([Date]),FORMAT([date],"MM")))

)

Please help me in understand the M script example. I have SQL script Query, do you want me to add the columns in Query? But As we need to pass value to where clause start and end date or only end date based on slicer selected value so that we can have refreshed data based on selected dates.

User can use any date selection such as startdate and enddate on timeline slicer :

2019-04-10  between  2019-04-10

2018-12-05 between 2018-12-13

Apologies! But I am trying to understand Where the below mentioned columns needs to created and how to use these column to achieve the goal:

InsertRelativeMonth = Table.AddColumn(InsertRelativeQuarterDescription, "Relative Month",

                                    each 12*([Year]-Date.Year(CurrentDate)) + ([Month Of Year]-Date.Month(CurrentDate))),

    InsertRelativeMonthDescription = Table.AddColumn(InsertRelativeMonth, "Relative Month Description",

                                    each if [Relative Month] = 0 then "Current Month"

                                        else if [Relative Month] = -1 then "Last Month"

                                        else if [Relative Month] = 1 then "Next Month"

                                        else if [Relative Month] < -1 then Number.ToText(Number.Abs([Relative Month])) & " Months Back"

                                        else Number.ToText([Relative Month]) & " Months Ahead"),

Please help me in understand what suppose to be placed for Bold sections , it will be helpful if can elaborate and briefly share the idea. 

Thanks,

Neha Sharma

[Anonymous]

Hi @NehaSha, 

First, I need to clarify a few things:

-Slicers affect the visuals on the report/dashboard. They do not actually query the SQL Query. Your data is not "refreshed" but the data in your model is filtered according to the selection in your slicers. So, bring all of the information that you may need in your SQL query - don't limit it to potential users selections.

-I suggested some M code, but your date table is DAX-based. Here is the DAX code:

Calendar = 
var today = TODAY()
return ADDCOLUMNS (
    CALENDAR (MIN(sitelink_site_summary_daily_history[start_date]),MAX(sitelink_site_summary_daily_history[start_date])),
    "DateKey", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MM" ),
    "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
    "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
    "MonthNameShort", FORMAT ( [Date], "mmm" ),
    "MonthNameLong", FORMAT ( [Date], "mmmm" ),
    "DayOfWeekNumber", WEEKDAY ( [Date] ),
    "DayOfWeek", FORMAT ( [Date], "dddd" ),
    "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
    "MMMYY", FORMAT([date],"MMM YY"),
    "SortOrder",INT(CONCATENATE(YEAR([Date]),FORMAT([date],"MM"))),
    "Relative Month", DATEDIFF(today,[Date], MONTH),
    "Relative Month Description", SWITCH(
        TRUE(), 
        DATEDIFF(today,[Date], MONTH) = 0, "Current Month", 
        DATEDIFF(today,[Date], MONTH) = -1, "Last Month", 
        DATEDIFF(today,[Date], MONTH) = 1, "Next Month", 
        DATEDIFF(today,[Date], MONTH) <-1, ABS(DATEDIFF(today,[Date], MONTH)) & " Months Back", 
        DATEDIFF(today,[Date], MONTH) & " Months Ahead"
    )
)

You can then add 2 slicers like this, and select "Current Month" for the "Relative Month Description" slicer. If users don't want to limit to current month, they can click it to turn it off, but it will be the default.

Cheers!

Nathan

[NehaSha]

Hi @Anonymous  ,

Thanks you very much to clarify more in detail.

I agree that slicers is used to filter the data rather than get refreshed data.

That’s the reason I would like to ask is there any possibility to pass the selected value from slicer to the manage parameters ?

If somehow I can pass the selected values to parameters or in other words to make dynamic date parameters and value can passed through selected value of slicer.

As this request was not initially requested I created the automated date update with default values and  get all calculations completed in sql query rather than on power bi platform. But if now I will get the all data to power bi I need to start from scratch to get half of calculations based on end date and half of measure based on start and end date and all the calculation for sameperiod LY and then we need to present the matrix table and finding the difference for all the columns between TY and LY. Which need to present “show on rows”.  

Appreciate for help!

Thanks,

Neha Sharma

[Anonymous]

@NehaSha - I agree that re-work is unfortunate, but the real Power of Power BI is the dynamic nature of measures.