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 t...
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) 

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 😞