Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to calculate YTD for average Values

Hi 

I need YTD measure for the below calculation.

(The average of the  sum of Total Material Generated / The average of  sum of Normalization Unit) .i  created the below measure

 

2019 YTD =AVERAGE('Intensity '[Total Material Generated])/AVERAGE('Intensity '[Normalization Unit])),YEAR(Intensity '[Year])=2019. but it doesnt help.
 
data protection
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    For YTD, try using time intelligence with Date calendar

    Refer Example

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

     To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Amit,

       

      Problem is the averege calculation... i hve to calcuate the averege values divide by average value on the particular year.

      example.. i used date filter for 2019

      .then i calculated the sum of  total material /count  = avrge value1...

      and sum of normal unit/count  for avege Value 2......

      then i divided avrge value1 and averge value 2.

       

      thanks

      kavitha

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Does that meet your requirement?

    2019 YTD = AVERAGE(Intensity[Normalization Unit])/CALCULATE(AVERAGE(Intensity[Total Material Generated]),ALLSELECTED(Intensity))