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

Hi , 

 

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

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

    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. 

     

  • 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