cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Denis_Slav
Helper II
Helper II

Determinate if dates in range with used slicer

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. 

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User III
Super User III

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.