Forum Discussion

OwaisAkber's avatar
OwaisAkber
Frequent Visitor
4 years ago
Solved

Get Max date below to the context filter

I need to get total purchase quantity from purchase table for last 12 months but with two conditions. i. 12 months count will start from last date of purchase ii. Purchase should not be included fr...
Need Help
  • Hoangechip910's avatar
    Hoangechip910
    4 years ago

    Sorry, my mistake. Please try this one

    var min_day_slicer = if (ISFILTERED('calerdar[date]), MIN ('calerdar[date]), MAX ('calerdar[date]))

                  return MAXX(CALCULATETABLE(purchase,REMOVEFILTER('calerdar[date]),'purchase[trnx_date] < min_day_slicer),'purchase[trnx_date])

  • OwaisAkber's avatar
    OwaisAkber
    4 years ago

    I tried below code and it worked.

    calculate(
        max(purchases[trnx_date]),
        datesinperiod('calendar'[date],startofmonth('calendar'[date])-1,-48,MONTH)
)

     

    but letter Hoangechip910 has given a better solution and I adopted the same. Below is his code.

    var min_day_slicer = if (ISFILTERED('calendar'[date]), MIN ('calendar'[date]), MAX ('calendar'[date]))
return MAXX(
    CALCULATETABLE
        (
            purchases,
            REMOVEFILTERS('calendar'[date]),
            'purchases'[trnx_date] < min_day_slicer
        ),
        'purchases'[trnx_date]
    )

     

    Thank you everyone for your time and support. ❤️