Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
ID | SaleDate | NetSales |
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:
ID | SaleDate | NetSales | Notes |
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:
ID | Total Sales | Date 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.
Solved! Go to Solution.
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.
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:
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |