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 ,

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'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