Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with Measure

Hi All,   Need some help with writing a measure for the below scenario.   I want the average of the values to be assigned to the end of the month.   If there are no values for a certain month, ...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    In the previous table I set the Value of Altona North and Armadale to be the same as Alexandria to save time, so there will be no change when filtering.

     

    I have adjusted the data, please download the latest attachment.

     

    Best Regards,

    Community Support Team _Charlotte

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak thanks so much for your solution

 

However, I don't need a measure in the date table but need to write a measure something similar to this:

https://community.powerbi.com/t5/Desktop/Showing-Previous-Month-Current-Month-Values-in-Future-Month/m-p/512578


I've written this formula, it's a bit close but yet far 🙂

 

Test =
VAR lastDateHasValues =
CALCULATE (LASTNONBLANK ('DIM-Date'[Date],CALCULATE ( AVERAGE( CQF[Value] ) ) ), ALL ( 'DIM-Date' ))


RETURN
IF ( ISBLANK ( average( CQF[Value] ) ), CALCULATE (average( CQF[Value] ) ,FILTER ( ALL ( 'DIM-Date' ), 'DIM-Date'[Date] = ( lastDateHasvalues ))),
average( CQF[Value] )
)
 
  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , lastnonblank should work , just at [Date] <= max([date])  //with table name

    to ensure only past dates are taken

     

    like

    CALCULATE (LASTNONBLANK ('CQF'[Date],CALCULATE ( AVERAGE( CQF[Value] ) ) ), filter( ALL ( 'DIM-Date' ), 'DIM-Date' [Date] = max( 'DIM-Date' [Date] )))

     

    Hope this can help

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      hi amitchandak ,

       

      I'm a bit cconfused now 🙂

      Are you able to be a bit more clearer?

       

      Thanks so much for your time again

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      hi amitchandak 

       

      when I use the following formula the format goes to date format, and unable to change it to %

       

      test 3 = CALCULATE (LASTNONBLANK ('CQF'[Date],CALCULATE ( AVERAGE( CQF[Value] ) ) ), filter( ALL ( 'DIM-Date' ), 'DIM-Date' [Date] <= max( 'DIM-Date' [Date] )))