Measure of Maximum over last months

[minerito]

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

[amitchandak]

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

[minerito]

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

[v-yiruan-msft · Accepted solution]

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

[leahYan]

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?