Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX command

Hello, 

i need create new column:  values under same project (rivile_code) show all values with type "Faktas" in column "Tipas", and then values with type "Planas" only if on the same day there is no values with type "Faktas". i.e. on 2020-06-30 - 76500,65 ..... but in 2021-05-31 - 73636,36 and so on.

thank you for help.

 

 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    Anonymous try this in a calculated column

    =IF(Table[Tipas]="Planas")&&Table[value],IF(COUNTROWS(FILTER(Table,Table[Tipas]="Faktas"&&Table[Data]=EARLIER(Table[Data])&&Table[rivile_code]=EARLIER(Table[rivile_code]))),"Yes","No"))

  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hi, Anonymous , you might want to try this formula in a calculated column

    Column =
VAR __t =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[rivile_code], 'Table'[Data] )
    )
VAR __t_faktas =
    FILTER ( __t, 'Table'[Tipas] = "Faktas" )
RETURN
    IF (
        COUNTROWS ( __t_faktas ),
        CALCULATE ( MAX ( 'Table'[value] ), __t_faktas ),
        CALCULATE ( MAX ( 'Table'[value] ), FILTER ( __t, 'Table'[Tipas] = "Planas" ) )
    )

5 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    5 years ago

    Anonymous try this in a calculated column

    =IF(Table[Tipas]="Planas")&&Table[value],IF(COUNTROWS(FILTER(Table,Table[Tipas]="Faktas"&&Table[Data]=EARLIER(Table[Data])&&Table[rivile_code]=EARLIER(Table[rivile_code]))),"Yes","No"))

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a new column like

    if(isblank([values]) ,
    sumx(filter(Table, [Tipas] ="Planas" && [rivile_code] = earlier([rivile_code]) && [Data] =earlier([Data])),[values]),[values])

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, Anonymous , you might want to try this formula in a calculated column

    Column =
VAR __t =
    CALCULATETABLE (
        'Table',
        ALLEXCEPT ( 'Table', 'Table'[rivile_code], 'Table'[Data] )
    )
VAR __t_faktas =
    FILTER ( __t, 'Table'[Tipas] = "Faktas" )
RETURN
    IF (
        COUNTROWS ( __t_faktas ),
        CALCULATE ( MAX ( 'Table'[value] ), __t_faktas ),
        CALCULATE ( MAX ( 'Table'[value] ), FILTER ( __t, 'Table'[Tipas] = "Planas" ) )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you for your time and solution guys! Good day.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    just thanked but stucked after next few steps til nice finish. Asking for help about wrong total in matrix. I watched all videos and post's about that problem, but in all of them there are too simple examples, and in my situation can't find how to apply them. Thank you in advance.

    Formula for better understanding:

     

    Sutarčių vertė = SUMX(GROUPBY('kp_database project_contract','kp_database project_contract'[contract_number], 'kp_database project_contract'[name.1], "MAXSUM",maxx(CURRENTGROUP(),'kp_database project_contract'[Custom.1])),[MAXSUM])
     ----->
    Sut likutis (NAUJAS) =
    IF(ISBLANK([Sutarčių vertė]),BLANK(),
    CALCULATE([Sutarčių vertė]-
    SUMX(GROUPBY('kp_database project_contract','kp_database project_contract'[contract_number],'kp_database project_contract'[name.1],'kp_database project_contract'[code],'kp_database project_contract'[name], 'kp_database project_contract'[year],'kp_database project_contract'[month],"MAXSUM",maxx(CURRENTGROUP(),'kp_database project_contract'[Likučio sąlyga])),[MAXSUM]),
    FILTER (ALL ( 'kp_database project_contract'[Data].[Date]),'kp_database project_contract'[Data].[Date] <= MAX('kp_database project_contract'[Data].[Date]))))