Need some guidance on report filtering

[Mattw112]

Hi we have a report that runs fine today.  It is filtrable on Week ending date.

But some records also contain a "Reporting Starting Date" in addition to their Week ending date.

I would like the report to only show records where the Reporting start date is null or occurs before the selected Week Ending date.

I do have a Calendar table if that helps.  But not sure how to tie together the selected Week ending date and the records that have a reporting start date.

Thanks,

Terry

[Mattw112]

I also have a slicer with Project Names....

Was just thinking is there a way for me to maybe filter that slicer with the projects where the Reporting start date is null or on or before the selected week ending date from the other slicer?

Terry

[Mattw112]

I've been doing some reading, would I need a calculated column or Calculated Measure maybe?  On which table or would it be a seperate table?

[v-yiruan-msft · Accepted solution]

Hi @Mattw112 ,

First, create a slicer base on the date field of Calendar table. Then create a measure as below and put this measure and the date field of Calendar table on your visual:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ticket] ),
    FILTER (
        'Table',
        'Table'[Reporting Starting Date] <= SELECTEDVALUE ( 'Calendar'[date] )
            && 'Table'[Week ending date] >= SELECTEDVALUE ( 'Calendar'[date] )
    )
)

If the above one is not working in your scenario, please provide some sample data in your model and expected result with examples or screenshots. Thank you.

Best Regards

[Mattw112]

Thanks ended up not doing int he calendar, ended up doing in the data table.  DAX looks pretty darn similar to what you posted.  Thanks.