Forum Discussion

Nick_2510's avatar
Nick_2510
Helper I
4 years ago
Solved

IF Conditions

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


IF THEN ELSE
  • tamerj1's avatar
    tamerj1
    4 years ago

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

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Nick_2510
      Helper I
      4 years ago

      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's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      tamerj1
      Community Champion
      4 years ago

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