cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## How to return 0, if criteria are not met in a measure, but for a specific period

Hello,

How to return 0, if criteria are not met in a measure, but for a specific period.

For example, I have try this, but it still not giving me the measure for the last 7 days, because I want that if for example in a specific date (Table[date]) there is not a person to be count, it must return me 0. So what it does is that it return me 0 but for all the months, not just for the last 7 days. :

VAR Last7DaysCount =
CALCULATE(
COUNT(Table[Persons]),
FILTER(
Table,
Table[Date] >= TODAY() - 6 && Table[Date] <= TODAY()
)
)
RETURN
IF(Last7DaysCount > 0, Last7DaysCount, 0)

1 ACCEPTED SOLUTION
Community Support

Hi @game1 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table

2. Update the formula of measure as below

``````Measure =
VAR _today =
TODAY () - 4
VAR _seldate =
SELECTEDVALUE ( 'Date'[Date] )
VAR Last7DaysCount =
CALCULATE (
COUNT ( 'Table'[Person] ),
FILTER ( 'Table', 'Table'[Date] = _seldate )
)
RETURN
IF (
_seldate >= _today - 6
&& _seldate <= _today,
IF ( Last7DaysCount > 0, Last7DaysCount, 0 ),
BLANK ()
)``````

3. Create a line chart visual (X-axis: [Date] field of date dimension table Y-axis: [Measure])

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
17 REPLIES 17
Regular Visitor

Hello @game1 ,

I worked with the following DAX. It worked.

`Last 7 Days Count =VAR last7Days =FILTER ( ALL ( Person ),Person[Date] >= TODAY () - 6 && Person[Date] <= TODAY ())RETURNIF ( CALCULATE ( COUNTROWS ( last7Days ), VALUES ( Person[Date] ) ) = BLANK (),0,CALCULATE ( COUNTROWS ( last7Days ), VALUES ( Person[Date] ) ))`
I created the table name "Person".
Please try this solution and let me know if any issues with it.

Thanks,
Rushika
Helper II

I have try this, but it give me 0 for the other months and day, but I only want for the specific period of the last 7 days, so it should only be 7 dates at the axis X.

Community Support

Hi @game1 ,

Base on your sample data, could you please provide your expected result? What will you want to display in the line chart? For example: Today(2024-2-23)

• If there is no person from 2023-2-16 to today,  it will be 0 for these dates. The other dates display the corresponding count?
• If there are some person from 2023-2-16 to today(there are 2 person in your sample data in last 7 days), it display the corresponding count for all dates?

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

Hello,

I'm still stuck with this problem. I've tried several options, but it doesn't seem to work.

Thanks!

Super User

After re-reading (and looking at your chart), I would use a calculated column in your date table like this  and use that for filtering that chart.

``````__Include 7 Days (CC) =
IF(
[Date] >= TODAY() - 6
&& [Date] <= TODAY(),
1,
0
)``````

( My earlier post is a more generic filter that I use regularly. )

Helper II

Hello,

Unfortunately, it return me the same result.

Super User

Hi @game1

After using the measure that gives you zeroes for dates outside of your range, you could add a column to your Date table.

This will include ALL dates in your table but can easily be changed.

``````__Include (CC) =
VAR _Oldest =
MINX(
ALL( 'TableP'[Date] ),
'TableP'[Date]
)
MAXX(
ALL( 'TableP'[Date] ),
'TableP'[Date]
)
VAR _Result =
IF(
[Date] >= _Oldest
1,
0
)
RETURN
_Result``````

Alternatively, you could write a measure like this:

``````__Include (measure) =
VAR _Curr = SELECTEDVALUE( 'Date'[Date] )
VAR _Oldest =
MINX(
ALL( 'TableP'[Date] ),
'TableP'[Date]
)
MAXX(
ALL( 'TableP'[Date] ),
'TableP'[Date]
)
VAR _Result =
IF(
_Curr >= _Oldest
1,
0
)
RETURN
_Result``````

In either case, you can filter your visual using the __Include (CC) = 1.

The calculated column is the easiest (for PBI) since it is only calculated during refresh.

Let me know if you have any questions.

Helper II

1-If there is no person from 2023-2-17 to today-Today(2024-2-23), it will be 0 for these dates. The other dates display the corresponding count? Yes

2-If there are some person from 2023-2-17 to today, it display it.

(there are 17(8+9) person in last 7 days for 2023-2-19.

3-And the other dates from 2023-2-17 to today, must be 0., except for 2023-2-19.

4-In this image, what is in red must not appear.

Community Support

Hi @game1 ,

Please update the formula of measure as below:

``````Measurex =
VAR _seldate =
SELECTEDVALUE ( 'Date'[Date] )
VAR Last7DaysCount =
CALCULATE (
COUNT ( Table[Persons] ),
FILTER ( Table, Table[Date] >= TODAY () - 6 && Table[Date] <= TODAY () )
)
RETURN
IF (
_seldate
>= TODAY () - 6
&& _seldate <= TODAY (),
IF ( Last7DaysCount > 0, Last7DaysCount, 0 ),
BLANK ()
)
``````

If the above ones can't help you get the expected result, please provide some sample data in your table (exclude sensitive data) with Text format and your expected result with special examples and screenshots.You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper II

It gave me this graph.

But, what I want is this one:

Community Support

Hi @game1 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table

2. Update the formula of measure as below

``````Measure =
VAR _today =
TODAY () - 4
VAR _seldate =
SELECTEDVALUE ( 'Date'[Date] )
VAR Last7DaysCount =
CALCULATE (
COUNT ( 'Table'[Person] ),
FILTER ( 'Table', 'Table'[Date] = _seldate )
)
RETURN
IF (
_seldate >= _today - 6
&& _seldate <= _today,
IF ( Last7DaysCount > 0, Last7DaysCount, 0 ),
BLANK ()
)``````

3. Create a line chart visual (X-axis: [Date] field of date dimension table Y-axis: [Measure])

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

Hi @game1 ,

In this case, if possible, when you create the line chart for last 7 days period then filter the line chart with relative date filter with respect to last 7 days. So everyday it will show you the line chart only specific with last 7 days of data only.

Helper II

Yes, I have already try this and it didn't change anything.

it return me the same graphic. And if I remove

`IF ( Last7DaysCount > 0, Last7DaysCount, 0 )`

I can't have 0 for the blank() date. So it is not working.

Super User

@game1

``````VAR __Today = TODAY ()
VAR __LastSevenDays = TODAY ()-6
VAR Last7DaysCount =
CALCULATE (
COUNT ( Table[Persons] ),
Table[Date] >= __LastSevenDays
Table[Date] <= __Today
)
RETURN
IF ( Last7DaysCount > 0, Last7DaysCount, 0 )``````

Did I answer your question? Mark my post as a solution! and hit thumbs up
Helper II

Hello,

I have add clarification ( see above).

Thanks!

Helper II

Hello,

exactly, I have already try that, but it is not giving me for the specify period of the last 7 days. It take all the month in january, I would 0 0 0 0... but it is not the period of the last 7 days.

Helper II

I just want it to return the me the period of the last 7 days and put 0 want the date is blank. But what it does is that it put 0 also for january, march,.....

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors