Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Need help to filter data in grid based on active date

Hello, I have an issue with Date ranges in my Power BI report. I have below requirement. I want to filter data from my Employment table where I have Employment start date and Employment end date. ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous ,

    By my tests based on your data sample, I'm afraid that your logic should be right.

    Assuming that I have the table like this and want to calculate the count of ID which is filtered by the slicer.

    I also create a calendar table with your formula and do not create the relationship for the two tables.

    Date = CALENDAR(date(YEAR(MIN(Employment[Employment start])),1,1),DATE(YEAR(MAX(Employment[Employment end])),12,31))

    Then I create the measure below.

    NosOfFTE = 
CALCULATE (
   COUNT(  'Employment'[ID] ),
    FILTER (
        Employment,
        Employment[Employment start] <= SELECTEDVALUE ( 'Date'[Date])
            && Employment[Employment end] >= SELECTEDVALUE ( 'Date'[Date] )
    )
)

    Here is the test output.

      

    Best  Regards,

    Cherry

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi Anonymous ,

By my tests based on your data sample, I'm afraid that your logic should be right.

Assuming that I have the table like this and want to calculate the count of ID which is filtered by the slicer.

I also create a calendar table with your formula and do not create the relationship for the two tables.

Date = CALENDAR(date(YEAR(MIN(Employment[Employment start])),1,1),DATE(YEAR(MAX(Employment[Employment end])),12,31))

Then I create the measure below.

NosOfFTE = 
CALCULATE (
   COUNT(  'Employment'[ID] ),
    FILTER (
        Employment,
        Employment[Employment start] <= SELECTEDVALUE ( 'Date'[Date])
            && Employment[Employment end] >= SELECTEDVALUE ( 'Date'[Date] )
    )
)

Here is the test output.

  

Best  Regards,

Cherry

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi  ,

     

    Thank you for your response. I think the issue is with me about date slicer only. I used date slicer between option. If I use list then it should work. I will check with List and let you know result.

     

    Kind regards,

    Parag