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
Anonymous
Not applicable

Date Filtering - +2 months from current date without including all months in between

Hi There, 

 

I have 3 pages where each of the pages should show only the specific values for "this month", "next month", "+2 month".

Example for this month:
Page 1: Shows only values for May 2022 ("this month")

Page 2: Shows only values for June 2022 ("next month")

Page 3: Shows only values for July  202 ("+2 month")

 

I have managed the first two pages with the relative date filter.

 

I have a problem with the third page, because if I make it with a relative filter, it includes the values for June AND July, as below and I only want to see July. 

swatsonlord_0-1651644595165.png

 

Below is an example of the cards I am using to show this data.

swatsonlord_1-1651644665675.png

 

Below are the related fields for baselines that I am using to attempt to get this data, both on the "DBeaver Connection" table.

Baseline #'s = MS110 Baseline #'s (whole number field)

Dates = MS110 (B) (date field)  

 

Any help would be appreciated! 

1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi @Anonymous,

 

You may try this solution.

1 Create a Measure

DateIn+2Month =
VAR CurrentMon =
    MONTH ( TODAY () )
VAR ExpectedMon = CurrentMon + 2
RETURN
    CALCULATE (
        COUNT ( 'Table'[MS110(B)] ),
        FILTER ( 'Table', MONTH ( 'Table'[MS110(B)] ) = ExpectedMon )
    )

 

2 Use this Measure as a visual filter, then only data for July will be displayed in the visual

vcazhengmsft_0-1652081470070.png

 

Also, attach the sample pbix as reference.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

View solution in original post

4 REPLIES 4
v-cazheng-msft
Community Support
Community Support

Hi @Anonymous,

 

You may try this solution.

1 Create a Measure

DateIn+2Month =
VAR CurrentMon =
    MONTH ( TODAY () )
VAR ExpectedMon = CurrentMon + 2
RETURN
    CALCULATE (
        COUNT ( 'Table'[MS110(B)] ),
        FILTER ( 'Table', MONTH ( 'Table'[MS110(B)] ) = ExpectedMon )
    )

 

2 Use this Measure as a visual filter, then only data for July will be displayed in the visual

vcazhengmsft_0-1652081470070.png

 

Also, attach the sample pbix as reference.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

There is gonna be an issue to this. What if current month is November 2023, the future 2nd month should be January 2024 right? 

what should we do to involve the Year in the calculation? Thanks!

Anonymous
Not applicable

Hey, 

Thanks for your response. That didn't seem to work in the format that I'm using though, could you explain further?

 

My baseline # field isn't currently a calculated measure, its just a count of the items in the column. 

Thanks, 
Siobhan 

amitchandak
Super User
Super User

@Anonymous , Add an additional filter to your measure 

 

measure 1 =

var _max = Eomonth(maxx(allselected('Date'), 'Date'[Date] ),0)

return

calculate([Measure], filter('Date', eomonth('Date'[Date] ,0) = _max) )

 

 

All measure two follow above, of use one measure like that as visual level filter and check for non blank

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.

Top Solution Authors