Forum Discussion

RPAI2812's avatar
RPAI2812
Helper I
4 years ago
Solved

Showing data with date in between initial and enddate

I have some data which each row is a initiative with INITIALDATE and ENDDATE columns. 

I also have a calendar table which I create a relationship with INITIALDATE at the moment.

However, I wanted to have a solution which in a YEAR slicer, if I select any year that is between the INITIALDATE and ENDDATE, the initiative still appears in the visual.

 

For example, Initiative 1 has:

INITIALDATE = 01/01/2020

ENDDATE = 31/12/2023

 

I wanted the initiative 1 to appear if I select any of the following years: 2020, 2021, 2022 and 2023.

 

Rgs

Need Help
  • moizsherwani's avatar
    moizsherwani
    4 years ago

    Hi RPAI2812 

     

    In this case you actually need to disconnect the link between the calendar table and your fact (data) table and then use measures to show and hide the initiatives as below. Put a visual filter on the table with "IniativeShow = 1"

     

    InitativeShow = 
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            SelectedYear >= YEAR ( 'Table'[Initial Date] )
                && SelectedYear <= YEAR ( 'Table'[End Date] )
        )
    )

     

     (I have also attached the file for your reference) 

2 Replies

  • moizsherwani's avatar
    moizsherwani
    Continued Contributor
    4 years ago

    Hi RPAI2812 

     

    In this case you actually need to disconnect the link between the calendar table and your fact (data) table and then use measures to show and hide the initiatives as below. Put a visual filter on the table with "IniativeShow = 1"

     

    InitativeShow = 
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            SelectedYear >= YEAR ( 'Table'[Initial Date] )
                && SelectedYear <= YEAR ( 'Table'[End Date] )
        )
    )

     

     (I have also attached the file for your reference) 

  • RPAI2812's avatar
    RPAI2812
    Helper I
    4 years ago

    moizsherwani 

    Thank you for the reply, it solved my issue.

    Now if you could give an extra help.. imagine I have a column with some savings from this initiative.

    How to filter a SUM or AVERAGE measure with Initiativeshow = 1?

    I tried to apply with calculate or filter, but could not manage 😞