Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mattw112
Helper I
Helper I

Need some guidance on report filtering

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

1 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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Mattw112
Helper I
Helper I

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

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?

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.