Forum Discussion

jcamilo1985's avatar
jcamilo1985
Helper III
5 months ago
Solved

cancel slicer dates

First of all, thank you so much to everyone who takes the time to read this post. I have the following scenario: two calendar tables. The first (calendar1) is connected to the fact table via the `fe...
Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    Step 1) Helper: Selected date (or today) and the 7-day window

    _Selected Date =
COALESCE ( SELECTEDVALUE ( calendar1[Fecha] ), TODAY() )

_In Window =
VAR Cutoff = [_Selected Date]
VAR StartD = Cutoff - 7
VAR EndD   = Cutoff - 1
VAR ThisD  = MAX ( calendar1[Fecha] )
RETURN
IF ( ThisD >= StartD && ThisD <= EndD, 1, 0 )

    Put "_In Window" in the visual-level filters and keep only = 1. This makes the table show only the 7 days prior.

     

    Step 2) Count by fecha_radicacion (calendar1)

    Orders (Radicacion) =
VAR Cutoff = [_Selected Date]
VAR StartD = Cutoff - 7
VAR EndD   = Cutoff - 1
RETURN
CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( calendar1[Fecha] ),
    DATESBETWEEN ( calendar1[Fecha], StartD, EndD )
)

     

    Step 3) Count by fecha_solucion (calendar2) without being restricted by filing date

    Orders (Solucion) =
VAR DatesOnAxis = VALUES ( calendar1[Fecha] )
RETURN
CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( calendar1 ),
    TREATAS ( DatesOnAxis, calendar2[Fecha] )
)

     

cengizhanarslan's avatar
cengizhanarslan
Super User
5 months ago

Step 1) Helper: Selected date (or today) and the 7-day window

_Selected Date =
COALESCE ( SELECTEDVALUE ( calendar1[Fecha] ), TODAY() )

_In Window =
VAR Cutoff = [_Selected Date]
VAR StartD = Cutoff - 7
VAR EndD   = Cutoff - 1
VAR ThisD  = MAX ( calendar1[Fecha] )
RETURN
IF ( ThisD >= StartD && ThisD <= EndD, 1, 0 )

Put "_In Window" in the visual-level filters and keep only = 1. This makes the table show only the 7 days prior.

 

Step 2) Count by fecha_radicacion (calendar1)

Orders (Radicacion) =
VAR Cutoff = [_Selected Date]
VAR StartD = Cutoff - 7
VAR EndD   = Cutoff - 1
RETURN
CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( calendar1[Fecha] ),
    DATESBETWEEN ( calendar1[Fecha], StartD, EndD )
)

 

Step 3) Count by fecha_solucion (calendar2) without being restricted by filing date

Orders (Solucion) =
VAR DatesOnAxis = VALUES ( calendar1[Fecha] )
RETURN
CALCULATE (
    COUNTROWS ( Fact ),
    REMOVEFILTERS ( calendar1 ),
    TREATAS ( DatesOnAxis, calendar2[Fecha] )
)

 

jcamilo1985's avatar
jcamilo1985
Helper III
5 months ago

Thanks for replying.
I didn't get a chance to test this solution; Copilot marks it as the solution path.
However, what I did to work around it was create an additional calendar table, really just the date column. I linked this table using an active relationship to the filing date and an inactive relationship to the resolution date. I use these dates in the visual editor, then in DAX, with the active context of the main dates table, I create a small table with the dates I need. Then, using CALCULATE, I remove the filters from the other tables and pass the previously created table, and voila!

The new calendar table is small, so it doesn't impact storage or performance.