Forum Discussion

JamesBurke's avatar
JamesBurke
Icon for Helper III rankHelper III
2 years ago

Max Month -1

Hi ,    Max Month Usages = Var MaxMonthValue = CALCULATE(MAX('Date'[Year Month Number]) , FILTER('Date',[UsagesCount] >= 1)) RETURN CALCULATE([Total Kwh],'Date'[Year Month Number] = MaxMonthVal...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
JamesBurke
Icon for Helper III rankHelper III
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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