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.

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 

 

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] )))