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 from the month/date given in report slicer (current context). 

 

My approach is to get last purchase date first meeting above conditions and then calculate purchase quantity by using DATESINPERIOD function.

 

To get last purchase date, e.g. in below image, date range is given from 1st Jul to 31st Jul and sku slicer has selected four SKUs.

below is the DAX to get this result.

 

var max_date = calculate(
        maxx(purchases,'purchases'[trnx_date]),
        purchases[trnx_type] = "Purchase",
        all('calendar'[date])

    )
    return max_date

 

 

According to my two conditions, dates in July and August should not reflect here, rater these purchas date should be might be in june or any other month. 

 

Can anyone help, how to achieve this?

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. ❤️

6 Replies

  • Hoangechip910's avatar
    Hoangechip910
    Frequent Visitor
    4 years ago

    Hi

    Measure

    max_date =

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

                  return MAXX(FILTER(purchase,'purchase[trnx_date] < min_day_slicer),purchase,'purchase[trnx_date])

  • Hoangechip910's avatar
    Hoangechip910
    Frequent Visitor
    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
      Frequent Visitor
      4 years ago

      Great. It worked and giving desired result. Thanks

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    Hi,

    Share some data and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

  • OwaisAkber's avatar
    OwaisAkber
    Frequent Visitor
    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. ❤️