cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
OwaisAkber
Frequent Visitor

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

 

 

OwaisAkber_0-1660572095615.png

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?

2 ACCEPTED SOLUTIONS
Hoangechip910
Frequent Visitor

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])

View solution in original post

OwaisAkber
Frequent Visitor

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

View solution in original post

6 REPLIES 6
OwaisAkber
Frequent Visitor

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

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Hoangechip910
Frequent Visitor

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])

Great. It worked and giving desired result. Thanks

OwaisAkber
Frequent Visitor

It did not work. Nothing returned now.

Hoangechip910
Frequent Visitor

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])

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors