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.

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.