Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help in time related DAX

Hey Experts,

 

I need help with dax.

 

I have two columns one is SALE DATE and one is ORDER DATE. I need one column or measure according below condition,
Ever order date has multiple sale date.
My sale date always should be last thursday of month. so whatever order date is there whichever coming sale date is "current" for me and otherones are "Next" for me.

example:

Order dateSale dateExp(REQUIRED MEASURE)
16/03/2022|||31/03/2022(last thursday of march)|||Current
16/03/2022|||28/04/2022(last thursday of april)|||Next
22/04/2022|||28/04/2022(last thursday of april)|||Current
22/04/2022|||26/05/2022(last thursday of may)|||Next
22/04/2022|||30/06/2022(last thursday of jun)|||Next
29/04/2022|||26/05/2022(last thursday of may)|||Current
29/04/2022|||30/06/2022(last thursday of jun)|||Next
   


I hope uou clear withrequirment. I want EXP as measure.

Urgent Help with DAX
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Anonymous ;

    Try it.

    result = 
VAR _min =
    CALCULATE ( MIN ( Data[Sale date] ),FILTER(ALLSELECTED('Data'),[Order date]=MAX([Order date])))
RETURN
    IF (
        MAX(  Data[Sale date] ) = _min,
        "Current",
        "Next")

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

12 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

    Expected result measure: =
VAR currentorderdate =
    MAX ( Data[Order date] )
VAR commingsalesdate =
    CALCULATE ( MIN ( Data[Sale date] ), ALLEXCEPT ( Data, Data[Order date] ) )
VAR result =
    IF (
        SELECTEDVALUE ( Data[Order date] ) = currentorderdate
            && SELECTEDVALUE ( Data[Sale date] ) = commingsalesdate,
        "Current",
        "Next"
    )
RETURN
    IF ( HASONEVALUE ( Data[Order date] ), result )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Not Working in my case.

      Distribution2 =
      VAR currentdate =
      MAX ( Sheet1[Date] )
      VAR commingexpdate =
      CALCULATE ( MIN ( Sheet1[Exp] ), ALLEXCEPT ( Sheet1, Sheet1[Date] ) )
      VAR result =
      IF (
      SELECTEDVALUE ( Sheet1[Date] ) = currentdate
      && SELECTEDVALUE ( Sheet1[Exp] ) = commingexpdate,
      "Current",
      "Next"
      )
      RETURN
      IF ( HASONEVALUE ( Sheet1[Date] ), result )



      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        4 years ago

        Please share your sample.

        I think the sample that is described in your question is different than what you show in the reply.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous ;

    Try it.

    result = 
VAR _min =
    CALCULATE ( MIN ( Data[Sale date] ),FILTER(ALLSELECTED('Data'),[Order date]=MAX([Order date])))
RETURN
    IF (
        MAX(  Data[Sale date] ) = _min,
        "Current",
        "Next")

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    You can try this calculated column

     

     

    Distribution Column: =
VAR CurrentSaleDate = Data[exp]
VAR CurrentOrderDateTable =
    CALCULATETABLE ( Sheet1, ALLEXCEPT ( Sheet1, Sheet1[Date] ) )
VAR NumberOfSales =
    COUNTROWS ( CurrentOrderDateTable )
VAR FirstSaleDate =
    MINX ( CurrentOrderDateTable, Sheet1[exp] )
VAR Result =
    IF (
        NumberOfSales = 1,
        "Current",
        IF ( CurrentSaleDate = FirstSaleDate, "Current", "Next" )
    )
RETURN
    Result

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

       

      Measure working fine after adding filter value. But calculated column giving not proper result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello Anonymous 

     

    First make a Date dimension table (Date = CALENDARAUTO()) and make following relationship between 'Order Date' and 'Date':

    After that use following measure:

    Measure =
    VAR startdate =
    DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Order Date] ) ), MONTH ( SELECTEDVALUE ( 'Table'[Order Date] ) ), 1 )
    VAR enddate =
    DATE ( YEAR ( SELECTEDVALUE ( 'Table'[Order Date] ) ), MONTH ( SELECTEDVALUE ( 'Table'[Order Date] ) ) + 1, 1 ) - 1
    VAR maxthursday =
    CALCULATE (
    MAX ( 'Date'[Date] ),
    FILTER (
    ALL ( 'Date' ),
    'Date'[Date] >= startdate
    && 'Date'[Date] <= enddate
    && FORMAT ( 'Date'[Date], "dddd" ) = "Thursday"
    )
    )
    VAR conditionCurrentNext =
    IF (
    ( maxthursday = SELECTEDVALUE ( 'Table'[Sale Date] ) ),
    "Current",
    IF (
    (
    SELECTEDVALUE ( 'Table'[Sale Date] ) > maxthursday
    && SELECTEDVALUE ( 'Table'[Order Date] ) < maxthursday
    ),
    "Next",
    IF (
    (
    SELECTEDVALUE ( 'Table'[Order Date] ) > maxthursday
    && DATEDIFF (
    SELECTEDVALUE ( 'Table'[Order Date] ),
    SELECTEDVALUE ( 'Table'[Sale Date] ),
    DAY
    ) > 31
    ),
    "Next",
    "Current"
    )
    )
    )
    RETURN
    conditionCurrentNext
     
    The output I got with this measure matched your requirement:

     

     

    Please accept it as a solution if it matches your requirement🙂