Determinate if dates in range with used slicer

[Denis_Slav]

Hi All,

I need to marked several dates range in auto-genered [Calendar_Table] to use them like a filter for different table with not the same granularity of dates. This range should be determinating from Slicer value.

My action in next steps

1.Create measure to determinate last date in one of the table (in one of the source it means 1st date in month)

DT_LastDate_DS_TV = calculate(max(DS_TV_Audience[DT_DATE_TXT]);all(DS_TV_Audience))

2. Create measures:

DT_0_PeriodStart = 
    VAR periodcount     = calculate(DISTINCTCOUNT(DIC_Calendar[DT_YYYY-MM]);DIC_Calendar)
    VAR periodFiltred   = CALCULATE(MIN(DIC_Calendar[DT_DATE_TXT]);DIC_Calendar)
    VAR periodDefault   = value([DT_LastDate_DS_TV])-value(mid([DT_LastDate_DS_TV];7;2))
RETURN
    if(periodcount > 1; periodDefault;periodFiltred)

DT_0_PeriodEnd = 
    VAR periodcount   = calculate(DISTINCTCOUNT(DIC_Calendar[DT_YYYY-MM]);DIC_Calendar)
    VAR periodFiltred = CALCULATE(max(DIC_Calendar[DT_DATE_TXT]);DIC_Calendar)+6
    VAR periodDefault = [DT_LastDate_DS_TV]+31
RETURN
    if(periodcount > 1; periodDefault;periodFiltred)

3. Create slicer for [DT_YYYY-MM] column from DIC_CALENDAR table

4. Create new column in DIC_CALENDAR

DT_Period_0 = 
  VAR periodDefault_min = calculate(min(DIC_Calendar[DT_DATE]);Filter(DIC_Calendar;DIC_Calendar[DT_DATE_TXT]=[DT_LastDate_DS_TV]))
  VAR periodDefault_max = calculate(max(DIC_Calendar[DT_DATE]);Filter(DIC_Calendar;DIC_Calendar[DT_DATE_TXT]=[DT_LastDate_DS_TV]))
  VAR periodFiltred_min = [DT_0_PeriodStart]
  VAR periodFiltred_max = [DT_0_PeriodEnd]
  VAR countelement      = [CHK_FiltredPeriod]
 RETURN
   if([CHK_FiltredPeriod] = 1;TRUE;FALSE)

If I put [DT_0_PeriodStart] & [DT_0_PeriodEnd] use in visual, they correct determinate range of dates, which should be marked in case of used and not used slicer. But in column [DT_Period_0] it always give me FALSE result.

ALTERNATIVE WAY

1. I Create measures for each KPI

KPI_AvRch% = 
  VAR periodDefault_min = value([DT_LastDate_DS_TV])-value(mid([DT_LastDate_DS_TV];7;2))
  VAR periodDefault_max = [DT_LastDate_DS_TV]+31
  VAR periodFiltred_min = CALCULATE(MIN(DIC_Calendar[DT_DATE_TXT]);DIC_Calendar)
  VAR periodFiltred_max = CALCULATE(MAX(DIC_Calendar[DT_DATE_TXT]);DIC_Calendar)+6
RETURN 
    IF (
        ISFILTERED(DIC_Calendar[DT_YYYY-MM]);
        calculate(SUM(DS_TV_Audience[AvRch%]);filter(DIC_Calendar;DIC_Calendar[DT_DATE_TXT]>=periodFiltred_min&&DIC_Calendar[DT_DATE_TXT]<=periodFiltred_max));
        calculate(SUM(DS_TV_Audience[AvRch%]);filter(DIC_Calendar;DIC_Calendar[DT_DATE_TXT]>=periodDefault_min&&DIC_Calendar[DT_DATE_TXT]<=periodDefault_max))
    )

But this way is not comfortable, because I need a lot of different statistics and range selections. 

[MFelix · Accepted solution]

Hi @Denis_Slav .

You cannot use measure to create or filter out table columns directly, meaning that if you are using a measure to create a new value in a column this will always return incorrect values due to the context.

Measures are calculated when they are called and always depeding on the context so since a table is a fixed when you use a measure to calculate some information the result will always be the same.

Do you have a relationship between your calendar table and the other tables?

What is the exact result you want to achieve sometimes having the correct connection and the right slicers in place is the best way to make the calculations.

Can you share a sample file and expected result?