How get get the min and max value in a detailed table - similar to Partition by in SQL Server

[leorrn]

Hello Guys how are you?

Imagine that I have the following table structure: 

Product Description / Date (at day level) / Hour (24 in a day) / Volume

Summarized data:

My target is: when the user selects the month of March, for example, I should show the sum of Billed and Invoiced for all days / hours in the selected month (these are ok) 

But for reading, I need to split it into 2 measures:

• "Reading Last Month": the expected result should be the max date + hour of the previous month (Feb) and get the Volume value for that reading
• "This Month": the expected result should be the max date + hour of the current month (March) and get the Volume value for that reading

Example:

"Reading Last Month"

"This Month"

Therefore, the final result should be:

Links for the PBI file and Excel Data

I appreciate all the community help here 👊

[v-rongtiep-msft · Accepted solution]

Hi @leorrn ，

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _1 =
    MAXX (
        FILTER (
            ALL ( Table1 ),
            Table1[product_descr] = "Reading"
                && Table1[Year Month] = SELECTEDVALUE ( Table1[Year Month] )
        ),
        Table1[Hour]
    )
RETURN
    CALCULATE (
        MAX ( Table1[Final Volume] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Year Month] = SELECTEDVALUE ( Table1[Year Month] )
                && Table1[product_descr] = "Reading"
                && Table1[Hour] = _1
        )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.