Forum Discussion

sharong's avatar
sharong
Icon for Helper I rankHelper 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 202...
  • 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")


     

     

some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
3 years ago

Hi sharong no data sufficient to provide some sound possible solution, so please check link below and adjust your table / data columns name (see first example). Hope this help

https://dax.guide/firstdate/ 

sharong's avatar
sharong
Icon for Helper I rankHelper 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
    Icon for Community Champion rankCommunity 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")