IF Conditions

[Nick_2510]

Hi everyone!

I need to add one option in IF constraction...

switch off name if name in a period - data for this is in another table with colomns (Name, Start_period, End_Period)

working constration:

Count of -ve =

SUMX (

CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Data[Name] ) ),

IF ( [diff_AvSal_bal] < -1, 1, 0 )

)

I try to add condition  in IF constraction, but it doesnt work

Count of -ve =

SUMX (

CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Data[Name] ) ),

IF ( [diff_AvSal_bal] > 0 OR 'Календарь'[Date] IN DATESBETWEEN('Sheet1'['Start_per'], 'Sheet1'['End_per']'), 0, 1 )

)

[tamerj1]

@Nick_2510 
Here you go https://we.tl/t-j2udW5gRf0

OOS = 
SUMX (
    CROSSJOIN ( VALUES ('Календарь'[Date]), VALUES ( Glossary[Name] ) ),
    IF ( [diff_AvSal_bal] < -1 
        || 'Календарь'[Date] IN 
        CALENDAR ( 
            CALCULATE ( SELECTEDVALUE ( delist[Start_per] ) ), 
            CALCULATE ( SELECTEDVALUE ( delist[End_per] ) ) 
        ),
    1, 0 )
)

[tamerj1]

@Nick_2510 
This one is the latest solution I shared. Please download the file and check

[tamerj1]

Hi @Nick_2510 
Please try

Count of -ve =
SUMX (
    CROSSJOIN ( VALUES ( 'Календарь'[Date] ), VALUES ( Data[Name] ) ),
    CALCULATE (
        IF (
            [diff_AvSal_bal] > 0
                || 'Календарь'[Date]
                    IN CALENDAR ( MAX ( 'Sheet1'[Start_per] ), MAX ( 'Sheet1'[End_per] ) ),
            0,
            1
        )
    )
)

[Nick_2510]

@tamerj1 

It almost works, but the last month (Apr 2022) doesnt calculate...in total(....it is really strange...because with other months everything is OK

[tamerj1 · Accepted solution]

@Nick_2510 
Please try

Count of -ve =
SUMX (
    CROSSJOIN ( VALUES ( 'Календарь'[Date] ), VALUES ( Data[Name] ) ),
    CALCULATE (
        IF (
            [diff_AvSal_bal] > 0
                || 'Календарь'[Date]
                    IN CALENDAR (
                        CALCULATE ( MAX ( 'Sheet1'[Start_per] ) ),
                        CALCULATE ( MAX ( 'Sheet1'[End_per] ) )
                    ),
            0,
            1
        )
    )
)

[tamerj1]

@Nick_2510 
One possiblity is having blank start dates.