cancel
Showing results 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

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
Super User

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

3 REPLIES 3
Super User

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 ) )

Resolver II

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?
Super User

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors