Measure minimum by day

Hi,

i've a sum of kms in a table, for each day of the month.

i'd like to get the min and the max by day of the month.

For example for the day 1

Max = 2902

Min = 192

For day 5

Max = 2721

Min = 1481

Do i Need a calculated table or a measure ?

How can i do ?

Need Help

GeekAlfPro

3 years ago

Hello,

I managed to build the calculate table, and slightly modified the measure.

As i want the min by day of the month, the final code of my measure is

@Min Kms Jour = 
var _table = CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            'Date',
            'Date'[Jour Numéro],
            'Date'[Année Mois]
        ),
        "Kms Electric",[Somme Kms Electric]
    ),
    ALLSELECTED('Date'[Année Mois]) //and not ALLSELECTED('Date'[Jour Numéro])
)
var _min = 
MINX(_table, [Somme Kms Electric])
return
_min

Thanks again Anonymous

3 Replies

Anonymous

Not applicable

3 years ago

Hi GeekAlfPro ,

You need an unconnected entry table and three measure.

Table.

Measures:

@MIN = 
VAR _table = 
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            'Calendar',
            'Calendar'[YearMonthSort],
            'Calendar'[Day]
        ),
        "MeasureSales",[@Sales]
    ),
    ALLSELECTED('Calendar'[YearMonthSort])
)
VAR _min = MINX(_table,[MeasureSales])
RETURN
_min

@MAX = 
VAR _table = 
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            'Calendar',
            'Calendar'[YearMonthSort],
            'Calendar'[Day]
        ),
        "MeasureSales",[@Sales]
    ),
    ALLSELECTED('Calendar'[YearMonthSort])
)
VAR _max = MAXX(_table,[MeasureSales])
RETURN
_max

@MAX&MIN = 
SWITCH(
    SELECTEDVALUE('Table'[Value]),
    "MAX",[@MAX],
    "MIN",[@MIN]
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Measure minimum by day.pbix144 KB

GeekAlfPro
Helper V
3 years ago
Thanks a lot Anonymous
i will try and let you know the result.

GeekAlfPro

Helper V

3 years ago

Hello,

I managed to build the calculate table, and slightly modified the measure.

As i want the min by day of the month, the final code of my measure is

@Min Kms Jour = 
var _table = CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            'Date',
            'Date'[Jour Numéro],
            'Date'[Année Mois]
        ),
        "Kms Electric",[Somme Kms Electric]
    ),
    ALLSELECTED('Date'[Année Mois]) //and not ALLSELECTED('Date'[Jour Numéro])
)
var _min = 
MINX(_table, [Somme Kms Electric])
return
_min

Thanks again Anonymous

Forum Discussion

Measure minimum by day