Forum Discussion

KrilleGTI's avatar
KrilleGTI
New Member
2 years ago
Solved

New user needs help with DAX

Hi 

I need some help on how to programme a new meassure or table/column.

I have a table with the columns named CompanyID, Values and Date.  I would like to calculate the difference in percentage between the average of values for the oldest date for each row. I have no problem creating it in Excel but I want to use it in Power bi together with a slider for CompanyIDs and Dates. Expecting the result is the orange coloured table.


 

/Christian

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, KrilleGTI 

     

    Based on your information, I create a table

     

     

    Then I create a new measure named Average value by date

     

     

    Based on your requirements, I've created a measure, and here's a preview of me

     

     

    Here is my DAX

    Measure = var a=MAXX(FILTER(ALLSELECTED('Table'),[Date]<MAX('Table'[Date])),[Date])

var b=CALCULATE([Average value by date],'Table'[Date]=MINX(ALLSELECTED('Table'),[Date]))

RETURN DIVIDE([Average value by date],b)-1

     

     

    How to Get Your Question Answered Quickly 

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • KrilleGTI's avatar
    KrilleGTI
    New Member
    2 years ago

    How can I attach my Power BI file? I have it almost working just need some help with pecentage calculation.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, KrilleGTI 

     

    Based on your information, I create a table

     

     

    Then I create a new measure named Average value by date

     

     

    Based on your requirements, I've created a measure, and here's a preview of me

     

     

    Here is my DAX

    Measure = var a=MAXX(FILTER(ALLSELECTED('Table'),[Date]<MAX('Table'[Date])),[Date])

var b=CALCULATE([Average value by date],'Table'[Date]=MINX(ALLSELECTED('Table'),[Date]))

RETURN DIVIDE([Average value by date],b)-1

     

     

    How to Get Your Question Answered Quickly 

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.