Forum Discussion
jcamilo1985
6 months agoHelper III
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...
- 6 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
6 months agoSuper User
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] )
)