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

[game1]

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)

[rushikale01]

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 ()
)
RETURN
IF ( 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

[game1]

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. 

[v-yiruan-msft]

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

[game1]

Hello, 

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

Thanks! 

[gmsamborn]

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

[game1]

Hello, 

Unfortunately, it return me the same result. 

But, thanks for your help! 

[gmsamborn]

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]
    )
VAR _Newest =
    MAXX(
        ALL( 'TableP'[Date] ),
        'TableP'[Date]
    )
VAR _Result = 
    IF(
        [Date] >= _Oldest
            && [Date] <= _Newest,
        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]
    )
VAR _Newest =
    MAXX(
        ALL( 'TableP'[Date] ),
        'TableP'[Date]
    )
VAR _Result = 
    IF(
        _Curr >= _Oldest
            && _Curr <= _Newest,
        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.

[game1]

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.

[v-yiruan-msft]

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

[game1]

It gave me this graph. 

But, what I want is this one: 

[v-yiruan-msft · Accepted solution]

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

[rushikale01]

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.

[game1]

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. 

[Fowmy]

@game1 

Not sure If I understood your question, please try:

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 )

[game1]

Hello, 

I have add clarification ( see above).

Thanks!

[game1]

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.

[game1]

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