Forum Discussion

sharong's avatar
sharong
Helper I
3 years ago
Solved

Dax for earliest date

Hi,

My requirement is, I have set of document numbers, thier sales amount, and thier posting date.

I need a measure for rolling 12 months value (for july 2023, I will consider july 2022 to july 2023 values) where the filter condition is , if a document number is repeated in that rolling 12 months , then I have to consider the first (earliest posting date in that 12 months period) date sales amount for calculation.

I am struck here with the following measure, where I am not able to implement the first date calculation

test2 =
var tables = CALCULATETABLE(datatable, DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -13, MONTH))
return
SUMX(tables, SUM(datatable[sales amount]))
 
Can anyone help me in this 
 
 
  • some_bih's avatar
    some_bih
    3 years ago

    Hi sharong  I created simple sum measure  Total Sales and 3 calculated columns.

    I manage to get your July 2023 data amount 750 but not for June 1074 as calculation of occurance is complex for single column and two more months. Hope this help

    Total Sales =
    SUM ( Sheet1[sales amount] )



    Posting Date Within Last 12 Months =
    //calculation of posting date in range of dates
    VAR __MaxPostingDate = MAX('Sheet1'[posting date])
    VAR __Last12MonthsStart = __MaxPostingDate - 365
    RETURN
        IF('Sheet1'[posting date] >= __Last12MonthsStart
        && 'Sheet1'[posting date] <= __MaxPostingDate,
        TRUE(), FALSE())
     
    Sequence by document =
    //sequence by document no and relevant posting date
    VAR CurrentDate = Sheet1[posting date]
    RETURN COUNTROWS (
        FILTER (
            CALCULATETABLE (
                Sheet1,
                ALLEXCEPT ( Sheet1, Sheet1[document no] ),Sheet1[Posting Date Within Last 12 Months]=TRUE()
            ),
            Sheet1[posting date] <= CurrentDate
           
        )
    )
    Relevant or Not Relevant =
    IF(Sheet1[Posting Date Within Last 12 Months]=TRUE() && Sheet1[Sequence by document]=1,"Relevant","Not Relevant")


     

     

5 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    3 years ago

    Hi sharong is your part var tables returning expected output?

    If yes then you should use
    CALCULATE (
    SUM(datatable[sales amount]),

    tables)

    Did I answer your question? Kudos appreciated / accept solution!

    • sharong's avatar
      sharong
      Helper I
      3 years ago

      No, I am not getting the expected out, since i didnt include the first date calculation. 
      if a document number [Column name: doc_no] is repeated in that rolling 12 months , then I have to consider the first (earliest posting date in that 12 months period) date sales amount for calculation.

      Can you please help in adding this filter condition into the current dax, so that I can check if its returning the correct output. Or if there is any other way of writing this logic, let me know kindly

    • sharong's avatar
      sharong
      Helper I
      3 years ago

      Hi Below is the sample data,

       

      document no            posting date                       sales amount
      1431st june 202289
      1564th june 202285
      1432nd july 202250
      1323rd aug 2022100
      1565th sep 2022600
      1325th dec 2022800
      1431st july 202320

       

      So in the calculation,

      1) I take only rolling 12 months, for July  2023, [I will consider july 2022 to july 2023]

      2) So first two rows will not be considered for calculation

      3) And now in remaining rows, document 143 & 132 are repeated. In this case, we have to take the first occuring date for those document numbers.

      for document 143, we consider , 2nd July 2022 sales value

      for document no 132, we consider, 3rd august 2022 sales value

      remaining document values will be same.

      So my expected output is:

                                              Jun-23                             Jul-23
      Rolling sales value1074750

       

      • some_bih's avatar
        some_bih
        Community Champion
        3 years ago

        Hi sharong  I created simple sum measure  Total Sales and 3 calculated columns.

        I manage to get your July 2023 data amount 750 but not for June 1074 as calculation of occurance is complex for single column and two more months. Hope this help

        Total Sales =
        SUM ( Sheet1[sales amount] )



        Posting Date Within Last 12 Months =
        //calculation of posting date in range of dates
        VAR __MaxPostingDate = MAX('Sheet1'[posting date])
        VAR __Last12MonthsStart = __MaxPostingDate - 365
        RETURN
            IF('Sheet1'[posting date] >= __Last12MonthsStart
            && 'Sheet1'[posting date] <= __MaxPostingDate,
            TRUE(), FALSE())
         
        Sequence by document =
        //sequence by document no and relevant posting date
        VAR CurrentDate = Sheet1[posting date]
        RETURN COUNTROWS (
            FILTER (
                CALCULATETABLE (
                    Sheet1,
                    ALLEXCEPT ( Sheet1, Sheet1[document no] ),Sheet1[Posting Date Within Last 12 Months]=TRUE()
                ),
                Sheet1[posting date] <= CurrentDate
               
            )
        )
        Relevant or Not Relevant =
        IF(Sheet1[Posting Date Within Last 12 Months]=TRUE() && Sheet1[Sequence by document]=1,"Relevant","Not Relevant")