How to create a date filter for current month and upcoming three months

[rachelbi]

Hi, I'd like to create a filter measure to show the current month and upcoming 3 month data in the power BI graph. Yet what I created turns the graph blank. And I doubt whether the data from several years on the same month number would be aggreated. Grateful to have your help.

Below is my measure: 

Date Filter = //current and upcoming 3 months

IF(

    COUNTROWS(

        FILTER(

            'W&L Opportunity',

            'W&L Opportunity'[Actual Close Date]>= TODAY() &&

            'W&L Opportunity'[Actual Close Date] <= EOMONTH(TODAY(), 3) &&

            YEAR('W&L Opportunity'[Actual Close Date]) = YEAR(TODAY())

        )

    ) > 0,

    1,

    0

)

[qqqqqwwwweeerrr]

Hi @rachelbi 

I have taken daily level dummy date data. 

Now, create a new table with date by using distinct(date column)
once you create new table create new measure like this 

Get selected date range =

var twomonths = EOMONTH(TODAY(),2)

var currentdate = EOMONTH(TODAY(),-1)

return CALCULATE(COUNTROWS(FILTER('Fact','Fact'[Period] > currentdate && 'Fact'[Period] <= twomonths)))

this is what got as an output.
If you since iabe only single date per month so in April 30 rows in may 31 and so on

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: https://www.youtube.com/@letssolveproblem

Regards

[rachelbi]

Thanks @qqqqqwwwweeerrr , i would like to make the graph for current month to upcoming three month, i.e. Apr 2024 to Jul 2024. Given that I only have Apr data at the moment, wonder how to make the graph to show May, June & July in x-axis even though they are empty right now? 

Many thanks again

[qqqqqwwwweeerrr · Accepted solution]

Hi @rachelbi 

I missunderstood your question. I did not able to to get full proof solution but this will help or you can work around a bit and get the reuqired calcualtion 
1. i have create the new table using this 

cal = CALENDAR(EOMONTH(TODAY(),-1) +1,EOMONTH(MAX('Fact'[Period]),2))
then create new column here only: 

Month Year = YEAR(cal[Date]) & "-" & MONTH(cal[Date]) and same in you actual table wehere you to count rows
now create this measre again in same table from where you want to count rows 

Get selected date range check =

var twomonths = EOMONTH(TODAY(),2)

var currentdate = EOMONTH(TODAY(),-1)

return CALCULATE(COUNTROWS(FILTER('Fact','Fact'[Period] > currentdate && 'Fact'[Period] <= twomonths)))
 then create this visual 

and from 
month year filed click show item with no data 

this is work around, this might be not 100% full proof solution but hope you got the the concept to approach solution 

Regards

[rachelbi]

thank you so much! I got it based on your logic 😊