cancel
Showing results for
Did you mean:
Frequent Visitor

## Measure of Maximum over last months

Hi everyone,
I just came up with the issue I am not skilled enough to solve 😉 It mainly involves making two nested calculations which I cannot deal with.

Let's say I have dataset with structure similar to following (ItemID, Date (sorry for my locale), Category)

What I need is to create a measure that would:

- count distinct ItemIDS in given month

- return maximum value of monthly count over last 2 months.

- it should be measure, as I want to include slicer with category (and many more categoric columns) so it could be dynamic.

I want to achieve following results in visuals (here an example without including any slicer or filter):

 01.01.2017 01.02.2017 01.03.2017 1.04.2017 1.05.2017 6 6 5 5 4

I have tried following:

``Measure1 = MAXX(FILTER(Table; DATESINPERIOD ( Table[Date]; MAX( Table[Date] );-2; MONTH)); DISTINCTCOUNT(Table[ItemID]))``

But it does not work.

Do you happen to know any trick?

Cheers,

M

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: Measure of Maximum over last months

Hi @minerito

Please try to create the below measure:

 Measure = VAR a =    MAX ( 'Table'[Date] )VAR b =    SUMMARIZE (        FILTER (            ALLSELECTED ( 'Table' ),            'Table'[Date]                >= DATE ( YEAR ( a ), MONTH ( a ) - 1, 1 )                && 'Table'[Date]                    <= DATE ( YEAR ( a ), MONTH ( a ) + 1, 1 )        ),        'Table'[Date],        "CountOfItems", DISTINCTCOUNT ( 'Table'[ItemID] )    )VAR c =    MAXX ( b, [CountOfItems] )RETURN    c

Best Regards

Rena

4 REPLIES 4

## Re: Measure of Maximum over last months

Hi Menerito, I am not sure if this link would be helpful for you https://community.powerbi.com/t5/Desktop/FILTER-vs-CALCULATETABLE/td-p/197093

I have a similar problem to you, but I need a column to return the value in last month of every single product. (FOR EXAMPLE, I need to return value from a [sale] column)I tried this and it doesn't work

lastmonth_sale = CALCULATE('table'[sale],FILTER(ALL('table'[sale],'table'[date] = PREVIOUSDAY(table'[date])))

or

lastmonth_sale = CALCULATE('table'[sale],FILTER(ALL('table'[sale],'table'[date] = EARLIER(table'[date],1)))

If anyone can help here?

Super User IV

## Re: Measure of Maximum over last months

Try like

``````Measure1 = MAXX(FILTER(values(Table[Date]); DATESINPERIOD ( Table[Date]; MAX( Table[Date] );-2; MONTH)); DISTINCTCOUNT(Table[ItemID]))
//OR
Measure1 = calculate(MAXX(FILTER(Table; DATESINPERIOD ( Table[Date]; MAX( Table[Date] );-2; MONTH)); DISTINCTCOUNT(Table[ItemID])),values(Table[Date]))``````

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges

In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin

Frequent Visitor

## Re: Measure of Maximum over last months

Hi,
Thanks for help - unfortunately neither of them works. The first one dragged into vidual yields in 'can't display the visual', the second one is the same...

Microsoft

## Re: Measure of Maximum over last months

Hi @minerito

Please try to create the below measure:

 Measure = VAR a =    MAX ( 'Table'[Date] )VAR b =    SUMMARIZE (        FILTER (            ALLSELECTED ( 'Table' ),            'Table'[Date]                >= DATE ( YEAR ( a ), MONTH ( a ) - 1, 1 )                && 'Table'[Date]                    <= DATE ( YEAR ( a ), MONTH ( a ) + 1, 1 )        ),        'Table'[Date],        "CountOfItems", DISTINCTCOUNT ( 'Table'[ItemID] )    )VAR c =    MAXX ( b, [CountOfItems] )RETURN    c

Best Regards

Rena

Announcements

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors