Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
game1
Helper II
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

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

vyiruanmsft_0-1709283579602.png

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.

View solution in original post

17 REPLIES 17
rushikale01
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 ()
)
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

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. 

Capture d’écran, le 2024-02-22 à 16.27.21.pngCapture d’écran, le 2024-02-22 à 16.29.10.pngCapture d’écran, le 2024-02-22 à 16.27.46.pngCapture d’écran, le 2024-02-22 à 16.29.36.png

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?

vyiruanmsft_0-1708676614124.png

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.

Hello, 

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

Thanks! 

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

 

Hello, 

Unfortunately, it return me the same result. 

But, thanks for your help! 

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.

 

 

 

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.

Capture d’écran, le 2024-02-23 à 08.27.00.png

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.

It gave me this graph. 

Capture d’écran, le 2024-02-26 à 18.49.02.png

But, what I want is this one: 

game1_0-1708992447480.png

 

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

vyiruanmsft_0-1709283579602.png

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.

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.

1.png

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

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

 




 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Hello, 

 

I have add clarification ( see above).

Thanks!

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.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors