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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
icturion
Resolver II
Resolver II

Only show values for complete months

Hi, 

I have made a power bi report in which the results per month are presented compared to the same month a year earlier or the budget for that month. At the moment the month we are still in the middle of is also presented, but these values ​​are always lower as long as the month is not yet at the end. Is there a way to only display a month when it's completely over?

 

should I solve this in my measures or through filter options. what is the best approach?

I myself am thinking of a calculated column with an if statement. which checks whether the date column contains a date that falls in a month that is in the past. if so a 1 else a 0. then so I can filter on 1. I just don't know how to write this formula.

1 ACCEPTED SOLUTION

@icturion ,

 

Complete month based Today =
var _max = if( day(today()) >=15 ,eomonth(today(),-1), eomonth(today(),-2) )
return
IF(Table[datecolumn] <=_max,1,0)

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@icturion ,

Complete month based Today =

var _max = if( eomonth(today(),0)=today(), Today() , eomonth(today(),-1)  )
var _min = eomonth(_max,-1)+1

return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date] >= _min && 'Date'[Date] <=_max ) )

 

Hi @amitchandak ,

 

based on your example I have now created the following and it works well:

Complete month based Today =
var _max = ifeomonth(today(),0)=today(), Today() , eomonth(today(),-1) )
return
IF(Table[datecolumn] <=_max,1,0)
 
I just heard from finance that the previous month may only be available if it is at least the 15th month of the current month. How can I best process this in the above formula?

@icturion ,

 

Complete month based Today =
var _max = if( day(today()) >=15 ,eomonth(today(),-1), eomonth(today(),-2) )
return
IF(Table[datecolumn] <=_max,1,0)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.