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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mohammadyousaf
Helper III
Helper III

Combining IF Statements

Hi, can someone please help to combine these two statements. 

 

Firs Statement:

 

IF(ISBLANK([Date.Actual.Start]),BLANK(),}
IF(ISBLANK([Date.Actual.Completion]),BLANK(),
(DATEDIFF([Date.Actual.Start],[Date.Actual.Completion],DAY)))) 

 

2nd Statment: 

 

IF( NOT( ISBLANK( [Date.Actual.Start] ) ),

IF(ISBLANK([Date.Actual.Completion]),

DATEDIFF([Date.Actual.Start], [If.Today],DAY)))

1 ACCEPTED SOLUTION

Hi @mohammadyousaf ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below to get the Days.Calculations:

Days.Calculations = 
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "@datediff",
            IF (
                ISBLANK ( [Actual Start] ),
                BLANK (),
                DATEDIFF (
                    [Actual Start],
                    IF ( ISBLANK ( [Actual Completion] ), TODAY () ),
                    DAY
                ) + 1
            )
    )
RETURN
    IF (
        ISFILTERED ( 'Table'[Task] ),
        SUMX ( _tab, [@datediff] ),
        MINX ( FILTER ( _tab, NOT ( ISBLANK ( [Actual Completion] ) ) ), [@datediff] )
    )

yingyinr_0-1634275080198.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

5 REPLIES 5
amitchandak
Super User
Super User

@mohammadyousaf , Try like

 

IF(ISBLANK([Date.Actual.Start]),BLANK(),
IF(ISBLANK([Date.Actual.Completion]),DATEDIFF([Date.Actual.Start], [If.Today],DAY),
(DATEDIFF([Date.Actual.Start],[Date.Actual.Completion],DAY))))

Thank you, I tried this as well but can not get my desired out put. 

 

I have multiple rows as below:

 

TaskActual StartActual Completion
A30-Sep-2021 30-Sep-2021
B6-Oct-2021 
   
   

 

So when I select A, it shows me 1 day as added +1 in the above statement 

When I select B, it shows me 7 days

 

But when none is selected, it doesnt calculate Actual - Today.  It just calculate 1 form A. What I am trying to achieve DATEDIFF from 30 Sep to Today. 

@mohammadyousaf , selected value, means you are looking for a measure? or for a column 

 

Can you share sample data and sample output in table format?

Measure.. I have chart with values A & B

so when I select A

mohammadyousaf_0-1634040092992.png

When I select B

mohammadyousaf_1-1634040125464.png

 

here when A or B not selected

it shows 1, and i want it to be MIN of Actal Start - Today() because B is still in progress and it should calculate unless there is a completion date. 

mohammadyousaf_2-1634040178206.png

 

 

Hi @mohammadyousaf ,

I created a sample pbix file(see attachment), please check whether that is what you want. You can create a measure as below to get the Days.Calculations:

Days.Calculations = 
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "@datediff",
            IF (
                ISBLANK ( [Actual Start] ),
                BLANK (),
                DATEDIFF (
                    [Actual Start],
                    IF ( ISBLANK ( [Actual Completion] ), TODAY () ),
                    DAY
                ) + 1
            )
    )
RETURN
    IF (
        ISFILTERED ( 'Table'[Task] ),
        SUMX ( _tab, [@datediff] ),
        MINX ( FILTER ( _tab, NOT ( ISBLANK ( [Actual Completion] ) ) ), [@datediff] )
    )

yingyinr_0-1634275080198.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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.