cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## Blank as Zero but only between first and last date which have a value greater 0

Hello together,

i am looking for a way to show blanks as zero, but only between the first and last date which have Values > 0.

Usually i do it with +0 but then i have the problem, that every value in calendar gets a 0.

i need something like a for loop:

for all dates which are > calculate(min(date), value > 0) && < calculate(max(date), value > 0),
value + 0,

value

Thanks for help!

Nico

6 REPLIES 6
Super User

@Nico78 , Try like

0 between range
Measure = var _1= SUM(Opportunity[Opportunity count]) +0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))

Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines
Frequent Visitor

doesn't get the expected solution.

Community Support

Hi, @Nico78

Please check the following methods.

Example data:

Table:

``Date = CALENDAR(DATE(2022,1,1),DATE(2022,1,31))``

Measure:

``````Measure =
VAR N1 =
SUM ( 'Table'[Value] ) + 0
VAR Mindate =
CALCULATE ( MIN ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
VAR Maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Date] ) )
RETURN
IF (
SELECTEDVALUE ( 'Date'[Date] ) >= Mindate
&& SELECTEDVALUE ( 'Date'[Date] ) <= Maxdate,
N1,
BLANK ()
)``````

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thanks for answering!

I think its the right way to go, but i have one problem left. In your example there ist no dimension table (except date table) to filter the fact table. But i have another column in my fact table which i have to filter with another dimension table. What now happens is that only the dates from the fact table get a zero as blank (which is good for the moment) but the measure does not recognize the min and max date for a specific dimension filter. This means that the minimum date value in fact table always gets a zero even if the minimum date for a specific dimension filter is much later.

Do you know a solution for this problem?

many many thanks!

Nico

Community Support

Hi, @Nico78

Can you provide a simple example file? Sensitive data can be provided for deletion. The desired result can also be shown with a picture.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Data model:

Result without a filter selection "Belegnummer" (as expected and correct):

Result with a filter selection "Belegnummer":

It starts at 31.12.2019 because thats the first date in table "Tätigkeiten". But with one filter selection in "Belegnummer" it should start at the first date, where a value is greater 0 for the specific "Belegnummer".

Hope it is understandable.

Thanks again

Nico

## Helpful resources

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors