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 change the data type of DATE column in the DATA table, and try the below Calcualted Column in the Report table.

 

Max Date new column =
VAR countriescondition =
INTERSECT (
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] ), Data[COUNTRY] ),
{ "INDIA", "US", "UK", "NEWL" }
)
VAR countriescondition2 =
INTERSECT (
SUMMARIZE ( FILTER ( Data, Data[ITEM] = Report[ITEM] ), Data[COUNTRY] ),
{ "XXX" }
)
VAR newtable =
FILTER (
Data,
Data[ITEM] = Report[ITEM]
&& DATA[COUNTRY]
IN { "INDIA", "US", "UK", "NEWL" }
&& Data[AREA CODE] = "A"
)
VAR newtable2 =
FILTER (
Data,
Data[ITEM] = Report[ITEM]
&& 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[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 you so much for your help and reply. It's working well but still there is some issue.

If same item has different sales code in report table then the current DAX giving wrong result but actual result is blanks. Please refer the snapshot.

How can I added sales code range in your exsiting DAX. The sales code column is common in both tables.

I am try to added sales code in your exsiting DAX but it giving error. 

 

  • 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.