Max Month -1

[JamesBurke]

Hi , 

Max Month Usages =

Var MaxMonthValue = CALCULATE(MAX('Date'[Year Month Number]) , FILTER('Date',[UsagesCount] >= 1))

RETURN

CALCULATE([Total Kwh],'Date'[Year Month Number] = MaxMonthValue)

Max Month -1 Usages =

Var MaxMonthValue = CALCULATE(MAX('Date'[Year Month Number]) -1 , FILTER('Date',[UsagesCount] >= 1))

RETURN

CALCULATE([Total Kwh],'Date'[Year Month Number] = MaxMonthValue)

UsagesCount = CALCULATE(COUNTROWS('Emporia Device Usage'))

These Measurments work unfiltred however when filtered to a specifc month the Max Month -1 returns blank and i'm not sure why , Any help would be Appericated ! 

Thanks !

[Anonymous]

Hi @JamesBurke ,

How is your 'Date'[Year Month Number] defined?
Try using the calculated column below to make sure it is a consecutive number.

Year Month Number = 'Date'[Year] * 12 + 'Date'[Month Number]

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--China Power BI User Group

[JamesBurke]

Hi , 

My Year Month Number is a consecutive number, I have a database pulled from Sql

[Anonymous]

Hi @JamesBurke 

Take the sample data you gave as an example:

202001 - 1 = 202000 (not 201912). This is why the empty set is returned, because [Year Month Number] does not contain these values.
Try creating a column using my logic above.
201912 --> 2019*12+12=24240
202001 --> 2020*12+1=24241
24241-1=24240

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--China Power BI User Group

[JamesBurke]

@Anonymous 

Max Month Usages =

Var MaxMonthValue =

CALCULATE(

    MAX('Date'[Year Month Number new]) ,

    FILTER('Date',[UsagesCount] >= 1),

    ALL('Date'))

    RETURN

CALCULATE(

    [Total Kwh],'Date'[Year Month Number new] = MaxMonthValue)

Max Month Usages -1 =

Var MaxMonthValue =

CALCULATE(

    MAX('Date'[Year Month Number new])-1 ,

    FILTER('Date',[UsagesCount] >= 1),

    ALL('Date'))

    RETURN

CALCULATE(

    [Total Kwh],'Date'[Year Month Number new] = MaxMonthValue)

This still returns Blank when selecting on a specfic month for some reason 

[Anonymous]

@JamesBurke ,

Please try this:

Max Month Usages -1 =
VAR MaxMonthValue = MAX('Date'[Year Month Number new])-1 ,
RETURN
CALCULATE([Total Kwh],'Date'[Year Month Number new] = MaxMonthValue)

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 -- China Power BI User Group

[JamesBurke]

Ahh , That makes more sense. Is there a simple way of creating a column for distinc value count ?

Sorry im quite new to dax still , in my mind it would be DistinctCount= (Year Month Number) but this returns a single value opposed to a count of each distinct value itself. 

[AmiraBedh]

In your case MaxMonthValue calculation for "Max Month -1" subtracts 1 from the maximum year-month number found in the data that meets the condition [UsagesCount] >= 1. This works well in an unfiltered context because it works on the entire dataset.

However, when you apply a filter to a specific month, this changes the dataset that MaxMonthValue operates on, likely resulting in a different maximum year-month number or potentially no suitable data at all after the subtraction. This is especially true if the filtered month is the actual maximum month in your dataset since subtracting one would point to a month that's not included in your filter context

Max Month -1 Usages =
VAR MaxMonthValue =
    CALCULATE(
        MAX('Date'[Year Month Number]) - 1,
        ALL('Date'),  // Ignores the filter context for this calculation
        'Date'[UsagesCount] >= 1
    )
RETURN
    CALCULATE(
        [Total Kwh],
        'Date'[Year Month Number] = MaxMonthValue
    )

[JamesBurke]

Thanks for the Quick response, 

I'm getting a Function Placeholder is being used Error ? 

[AmiraBedh]

Can you share your dataset ?

[JamesBurke]

I'm not able to share my dataset unfortunately 

[JamesBurke]

Max Month Usages -1 =

Var MaxMonthValue =

CALCULATE(

    MAX('Date'[Year Month Number])-1 ,

    ALL('Date'),

    FILTER('Date',[UsagesCount] >= 1))

RETURN

CALCULATE(

    [Total Kwh],'Date'[Year Month Number] = MaxMonthValue)

Does not have an error when using the Filter function however returns blank still