cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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 Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Fabric Community Update - April 2024

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

Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors