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 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
    Icon for Helper I rankHelper 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