Filter on dimensional Data based on Date Key

[joshua1990]

Hi community!

I have a dimensional table that I would like to filter based on a date filter.

The structure is like this:

Article	Department	Value
A	Packaging	5
B	Polishing	6
C	Shipping	5
A1	Assembly	5

Now, I would like to exclude / filter out based on the following conditions:

• If Date > 01/01/2023 then
  • Remove Packaging and Shipping
• If Date <= 01/01/2023 then
  • Remove Polishing
• Keep Assembly allways, regardles of Date Filter.

How would you do that? 

[some_bih · Accepted solution]

Hi @joshua1990 not tested with number. Still, try measure below

CALCULATE( SUM(Tabl[Value]),
FILTER('Date', 'Date'[KeyColumn]>DATE(2023,1,1)) && FILTER(Table, Table[Depatment] NOT IN {"Packaging","Shipping" )
)
+
CALCULATE( SUM(Tabl[Value]),
FILTER('Date', 'Date'[KeyColumn]<=DATE(2023,1,1)) && FILTER(Table, Table[Depatment] NOT IN {"Polishing" )
)
+

CALCULATE( SUM(Tabl[Value]),
FILTER(Table, Table[Depatment] IN {"Assembly " )
)

[sroy_16 · Accepted solution]

You can try this solution as well.
FOr this you need to have 2 DAX. One as a measure and another as a calculated column.
Below are the codes.

Measure:
Current Date Filter =
MAX('Date'[Date])

Calculated Column:

Filtered =
VAR CurrentDate = [Current Date Filter]
VAR IsPackagingOrShipping =
'DimensionalTable'[Department] IN {"Packaging", "Shipping"}
VAR IsPolishing =
'DimensionalTable'[Department] = "Polishing"
VAR IsAssembly =
'DimensionalTable'[Department] = "Assembly"

RETURN
IF(
IsAssembly,
1, -- Always include Assembly
IF(
CurrentDate > DATE(2023, 1, 1),
IF(
IsPackagingOrShipping,
0, -- Exclude Packaging and Shipping
1 -- Include other departments
),
IF(
IsPolishing,
0, -- Exclude Polishing
1 -- Include other departments
)
)
)

In your visual you can Drag the "Filtered" column to the Filters pane of your visual and set it to only include rows where "Filtered" is 1.

Try this and see if the code works.

[some_bih · Accepted solution]

Hi @joshua1990 not tested with number. Still, try measure below

CALCULATE( SUM(Tabl[Value]),
FILTER('Date', 'Date'[KeyColumn]>DATE(2023,1,1)) && FILTER(Table, Table[Depatment] NOT IN {"Packaging","Shipping" )
)
+
CALCULATE( SUM(Tabl[Value]),
FILTER('Date', 'Date'[KeyColumn]<=DATE(2023,1,1)) && FILTER(Table, Table[Depatment] NOT IN {"Polishing" )
)
+

CALCULATE( SUM(Tabl[Value]),
FILTER(Table, Table[Depatment] IN {"Assembly " )
)