Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DubD
Frequent Visitor

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 sales (NetSales) based on the dates chosen grouped by a "Date Range" column that specifies which date range the sales fell into.  However, it should also include prior year SaleDates with potentially 5 different scenarios:

 

1. [Start Date] <= SaleDate < [Current Date]  ->  Date Range = "[Start Date] through [Current Date]"

2. [Start Date] - 1yr <= SaleDate < [Current Date] - 1yr  ->  Date Range = "[Start Date] - 1yr through [Current Date] - 1yr"

3. [Current Date] - 1yr <= SaleDate <= [End Date] - 1yr  ->  Date Range = "[Current Date] - 1yr through [End Date] - 1yr"

4. [Start Date] - 2yr <= SaleDate < [Current Date] - 2yr  ->  Date Range = "[Start Date] - 2yr through [Current Date] - 2yr"

5. [Current Date] - 2yr <= SaleDate <= [End Date] - 2yr  ->  Date Range = "[Current Date] - 2yr through [End Date] - 2yr"

 

For Example, the following table:

 

IDSaleDateNetSales
125825  2022-07-01  4.25
125825  2021-11-19  14.75
125825  2021-10-16  2.95
125825  2021-08-17  5.9
125825  2021-08-16  5.9
125825  2021-06-22  2.95
125825  2021-06-08  8.85
125825  2020-09-13  12
125825  2020-06-13  5

 

When the user selects the following filters:

Start Date = 2022-05-01

Current Date = 2022-08-17

End Date = 2022-11-01

 

Then we should filter/aggregate the following:

 

IDSaleDateNetSalesNotes
125825  2022-07-01 4.25 Scenario 1
125825  2021-11-19 14.75 Excluded
125825  2021-10-16 2.95 Scenario 3
125825  2021-08-17 5.9 Scenario 3
125825  2021-08-16 5.9 Scenario 2
125825  2021-06-22 2.95 Scenario 2
125825  2021-06-08 8.85 Scenario 2
125825  2020-09-13 12 Scenario 5
125825  2020-06-13 5 Scenario 4

 

So the table visual/end result that is displayed to the user is:

 

IDTotal SalesDate Range
125825  4.25  2022-05-01 through 2022-08-16
125825  8.85  2021-08-17 through 2021-11-01
125825  17.7  2021-05-01 through 2021-08-16
125825  12  2020-08-17 through 2020-11-01
125825  5  2020-05-01 through 2020-08-16

 

My table has many other attributes in them, but getting this date range logic to work feels like the crux of my problem.  Have tried creating unrelated date tables to control these 3 date filters/parameters, but am struggling to 1) get the table to filter accordingly and 2) display the appropriate "Date Range" string based on the SaleDate especially since SaleDate is not displayed in the table visual.

1 ACCEPTED SOLUTION

Hi, @DubD 

The expected result you provide seems to be wrong.

 

 You can try steps as below:

1.Enter a Table:

veasonfmsft_0-1660644072127.png

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]))
)

veasonfmsft_1-1660644235728.png

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.

 

View solution in original post

2 REPLIES 2
DubD
Frequent Visitor

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?

Hi, @DubD 

The expected result you provide seems to be wrong.

 

 You can try steps as below:

1.Enter a Table:

veasonfmsft_0-1660644072127.png

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]))
)

veasonfmsft_1-1660644235728.png

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.