cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
minerito
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)image.png

 

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.201701.02.201701.03.20171.04.20171.05.2017
66554

 

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

maxinum value.JPG

Best Regards

Rena

View solution in original post

4 REPLIES 4
leahYan Advocate I
Advocate I

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
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
Connect on Linkedin





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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


minerito
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
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

maxinum value.JPG

Best Regards

Rena

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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