How to ignore and retain existing values of cell, on IF FALSE condition

[Anonymous]

I have two tables

Table1: Powerbi_OpenCases

Table 2: backlog_tracker

Both the tables don't have any relationship

Objective: I need to record the total open cases for the day in the backlog_tracker table

Table 1: Powerbi_OpenCases - Consists of all open tickets with the support

Table 2: Contains following fields

1. Date/time Field

2. Calculated Column "Xbacklog_updater2"

Xbacklog_updater2 = if ('Backlog_Tracker'[Date/Time].[Date] = TODAY(), SUM(PowerBi_OpenCases[backlog_Counter]),0)

Table 2 snapshot

1. The Date/Time field has all dates for the entire year 2020 (similar to calendar table)

2. xbacklog_updater - It tries to check if the date matches today and if yes, it goes to table 1 and gets the sum of the cases, which needs to be considered for backlog, on false it is presently updating to 0

The above situation is working fine except that on daily basis, I'm not able to retain the previous day value as that also gets reset to 0

So, my query is there a way we can ignore action of returning 0 when the condition is false. I would ideally want to return the current cell value, so that the same thing stays there.

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. Can you share sample data and sample output.

Refer:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos.

[Anonymous]

@Amit - thanks for offering to help.

Let me first try with an example in the same context explained earlier

1. I have open tickets data in powerbi_opencases table  - the number of tickets opened every day keeps changing. 

2. I have another table with the field date, which has dates listed till end of this year

3. Requirement is - I want to be able to take the count of total # of cases in powerbi_opencases and update the total count against the specific date the count was obtained

Example: 15/4/2020 - has total # of open cases are 939. 

Today

In this case, it is able to update the value as 939 for 15/4/2020  and for other dates it will update as 0 

Tomorrow

it will be update the value as XXX for 16/4/2020 and for other dates it will update as 0

so, 15/4/2020 value of 939 will get reset to 0

I don't want any date which already past to be included in this action. 

expectation is

15/4/2020 - should have 939

16/4/2020 - should have new value and list keeps growing. 

[v-gizhi-msft]

Hi,

Please try this:

Xbacklog_updater2 = 
IF (
    MAX ( backlog_tracker[Date] ) >= TODAY (),
    CALCULATE (
        SUM ( 'PowerBi_OpenCases'[backlog_Counter] ),
        FILTER (
            ALLSELECTED ( Powerbi_OpenCases ),
            [Date] = MAX ( backlog_tracker[Date] )
        )
    ),
    0
)

Best Regards,

Giotto

[Anonymous]

@v-gizhi-msft - thanks for helping. 

I tried this, it has updated the last record in my table, i.e., 30 December 2020

see snapshots

[v-gizhi-msft]

Hi,

Do you want to fix today(2020/4/15) as the record start date?
If so, please try this:

Xbacklog_updater2 =
IF (
    MAX ( backlog_tracker[Date] ) <= TODAY ()
        && MAX ( backlog_tracker[Date] ) >= DATE ( 2020, 4, 15 ),
    CALCULATE (
        SUM ( 'PowerBi_OpenCases'[backlog_Counter] ),
        FILTER (
            ALLSELECTED ( Powerbi_OpenCases ),
            [Date] = MAX ( backlog_tracker[Date] )
        )
    ),
    0
)

Best Regards,

Giotto

[Anonymous]

@v-gizhi-msft - Thanks for help!

I'm okay to take today's date as start date of the record. 

But remember, the update of record count has to keep on changing with date every day

I tried to implement your new suggestion and there were no records impacted. 

Xbacklog_updater8 =

IF (

MAX ( Backlog_Tracker[Date/Time] ) <= TODAY ()

&& MAX ( Backlog_Tracker[Date/Time] ) >= DATE ( 2020, 4, 15 ),

CALCULATE (

SUM ( PowerBi_OpenCases[backlog_Counter] ),

FILTER (

ALLSELECTED ( PowerBi_OpenCases ),

[Date/Time] = MAX ( Backlog_Tracker[Date/Time] )

)

),

0

)

[Anonymous · Accepted solution]

I found the solution myself. Thanks for everyone who tried help. The solution is if you use IF.EAGER, then you don't have specific false statement (it is optional)

Following syntax worked 🙂 

xXbacklog_updater2 = IF.EAGER('Backlog_Tracker'[Date/Time].[Date] = TODAY(), SUM(PowerBi_OpenCases[backlog_Counter]))