Forum Discussion

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Calculate MAX date based on the multiple columns

Data: I have a two tables are data and report In data table contain item, country, date, time, area code and sales code Country column contains INDIA, PAK, US, UK, AUS, SRI, CAND, NEWL...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Saxon10 

    Please kindly check the below which is for the Calculated Column.

    I am not quite sure if the below is the most optimized way to write, but I tried to come up with the solution.

     

    Max Date new column =
    VAR countriescondition =
    INTERSECT (
    SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] ),
    { "INDIA", "US", "UK", "NEWL" }
    )
    VAR countriescondition2 =
    INTERSECT (
    SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] ),
    { "XXX" }
    )
    VAR newtable =
    FILTER (
    Data,
    Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE]
    && DATA[COUNTRY]
    IN { "INDIA", "US", "UK", "NEWL" }
    && Data[AREA CODE] = "A"
    )
    VAR newtable2 =
    FILTER (
    Data,
    Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE]
    && DATA[COUNTRY] = "XXX"
    && Data[AREA CODE] = "B"
    )
    RETURN
    IF (
    COUNTROWS ( countriescondition ) = 4,
    MAXX ( newtable, Data[DATE] ),
    IF (
    COUNTROWS ( countriescondition2 ) = 1
    && COUNTROWS (
    SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] )
    ) = 1,
    MAXX ( newtable2, DATA[DATE] ),
    BLANK ()
    )
    )
     
     

    Hi, My name is Jihwan Kim.

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, Saxon10 

Please kindly check the below which is for the Calculated Column.

I am not quite sure if the below is the most optimized way to write, but I tried to come up with the solution.

 

Max Date new column =
VAR countriescondition =
INTERSECT (
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] ),
{ "INDIA", "US", "UK", "NEWL" }
)
VAR countriescondition2 =
INTERSECT (
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] ),
{ "XXX" }
)
VAR newtable =
FILTER (
Data,
Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE]
&& DATA[COUNTRY]
IN { "INDIA", "US", "UK", "NEWL" }
&& Data[AREA CODE] = "A"
)
VAR newtable2 =
FILTER (
Data,
Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE]
&& DATA[COUNTRY] = "XXX"
&& Data[AREA CODE] = "B"
)
RETURN
IF (
COUNTROWS ( countriescondition ) = 4,
MAXX ( newtable, Data[DATE] ),
IF (
COUNTROWS ( countriescondition2 ) = 1
&& COUNTROWS (
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] && DATA[SALES CODE] = REPORT[SALES CODE] ), Data[COUNTRY] )
) = 1,
MAXX ( newtable2, DATA[DATE] ),
BLANK ()
)
)
 
 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Saxon10's avatar
Saxon10
Icon for Post Prodigy rankPost Prodigy
5 years ago

Hi,

 

Thanks for your reply and help. It's working perfectly without any problems.