Forum Discussion

DubD's avatar
DubD
Frequent Visitor
4 years ago
Solved

Filter a table that contains one date field based on multiple date ranges and display chosen range

I'm looking to display an aggregation of sales data based on the SalesDate where the user specifies the following filters:    Start Date Current Date End Date   The table should show total sale...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, DubD 

    The expected result you provide seems to be wrong.

     

     You can try steps as below:

    1.Enter a Table:

    2.create measure like:

    Range = 
SWITCH(SELECTEDVALUE(Scenario[Scenario]),
 "Scenario 1",[Start Date]&" through "&[Current Date],
 "Scenario 2",[Start Date -1yr]&" through "&[Current Date -1yr],
 "Scenario 3",[Current Date -1yr]&" through "&[End Date -1yr],
 "Scenario 4",[Start Date -2yr]&" through "&[Current Date -2yr],
  "Scenario 5",[Current Date -2yr]&" through "&[End Date -2yr]
  )
    Scenario = SWITCH(
    TRUE(),
    [Start Date]<=[Sale Date]&&[Start Date]<[Current Date],"Scenario 1",
    [Start Date -1yr]<=[Sale Date]&&[Sale Date]<[Current Date -1yr],"Scenario 2",
    [Current Date -1yr]<=[Sale Date]&&[Sale Date]<=[End Date -1yr],"Scenario 3",
    [Start Date -2yr]<=[Sale Date]&&[Sale Date]<[Current Date -2yr],"Scenario 4",
    [Current Date -2yr]<=[Sale Date]&&[Sale Date]<[End Date -2yr],"Scenario 5","Excluded"
)
    Total sales = CALCULATE(SUM('Table'[NetSales]),FILTER('Table','Table'[ID]=MAX('Table'[ID])&&[Scenario]=SELECTEDVALUE(Scenario[Scenario]))
)

    Note: Scenario[Scenario] is indispensable in this table visual, otherwise other column data will be summarized, but you can reduce the column width to 0 to hide it. (The 'text wrap' option needs to be turned off)

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

DubD's avatar
DubD
Frequent Visitor
4 years ago

Still struggling with this one which is frustrating since it is trivial if using a SQL stored procedure or even a table-value function (some parameters, where clause filtering the date range, and a CASE statement to handle the "date range" value to display).  

 

I was able to filter and calculate the total Net Sales.  First, I created a view, vw_OpenToBuy, that unions the same records together with the only difference being a "SaleDateFilter" column that adds 0-2 years to the SaleDate.  This way, you can specify a date range against the SaleDateFilter column and it will find any records that had an original SaleDate in that range, in that range one year ago, or in that range two years ago.  Also, used three unrelated date tables to allow users to choose a date as a slicer.  Created a measure for each of these to identify the selected date:

SelectedDateStart = 
CALCULATE 
( 
	MIN(tblDateStart[Date]) ,
	ALLSELECTED(tblDateStart[Date])
)

 

Then, can total the NetSales with the following measure:

 

NetSalesAmount = 

VAR FilteredOpenToBuy =
    FILTER (
        ALL (
            'vw_OpenToBuy'[SaleDateFilter]
        ),
        'vw_OpenToBuy'[SaleDateFilter] >= [SelectedDateStart] && 'vw_OpenToBuy'[SaleDateFilter] <= [SelectedDateEnd]
    )
	
VAR Result =
    CALCULATE 
	(
        SUM ( vw_OpenToBuy[NetSalesAmount] ),
        FilteredOpenToBuy
    )

RETURN
    Result



However, still struggling to display the associated "date range" value with each appropriate record.  Almost feels like it needs to be a calculated column since it is record specific but calculated columns do not refresh whenever a new value/date is chosen.  Anyone have any ideas as to how to get this "date range" to populate and display?

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, DubD 

    The expected result you provide seems to be wrong.

     

     You can try steps as below:

    1.Enter a Table:

    2.create measure like:

    Range = 
SWITCH(SELECTEDVALUE(Scenario[Scenario]),
 "Scenario 1",[Start Date]&" through "&[Current Date],
 "Scenario 2",[Start Date -1yr]&" through "&[Current Date -1yr],
 "Scenario 3",[Current Date -1yr]&" through "&[End Date -1yr],
 "Scenario 4",[Start Date -2yr]&" through "&[Current Date -2yr],
  "Scenario 5",[Current Date -2yr]&" through "&[End Date -2yr]
  )
    Scenario = SWITCH(
    TRUE(),
    [Start Date]<=[Sale Date]&&[Start Date]<[Current Date],"Scenario 1",
    [Start Date -1yr]<=[Sale Date]&&[Sale Date]<[Current Date -1yr],"Scenario 2",
    [Current Date -1yr]<=[Sale Date]&&[Sale Date]<=[End Date -1yr],"Scenario 3",
    [Start Date -2yr]<=[Sale Date]&&[Sale Date]<[Current Date -2yr],"Scenario 4",
    [Current Date -2yr]<=[Sale Date]&&[Sale Date]<[End Date -2yr],"Scenario 5","Excluded"
)
    Total sales = CALCULATE(SUM('Table'[NetSales]),FILTER('Table','Table'[ID]=MAX('Table'[ID])&&[Scenario]=SELECTEDVALUE(Scenario[Scenario]))
)

    Note: Scenario[Scenario] is indispensable in this table visual, otherwise other column data will be summarized, but you can reduce the column width to 0 to hide it. (The 'text wrap' option needs to be turned off)

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.