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, the average of the previous month needs to be assigned to it.

 

Hope my request makes sense. Thanks in advance!

 

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.

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , try a measure like this date table

     

    new measure =
    calculate(Max([Value%]) , filter(all('Date'), 'Date'[Date] = maxx(filter(all('Date'),'Date'[Date]<= max('Date'[Date])), 'Table'[Date]) && 'Date'[Date] = eomonth('Date'[Date],0) ))

     

     

    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 :radacad sqlbi My Video Series Appreciate your Kudos.

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Anonymous 

     

    You can try the following methods.

    Last date = 
DATE(YEAR([Date]),MONTH([Date])+1,1)-1

    Measure = 
VAR _a =
    SUMMARIZE (
        FILTER ( ALL ( CQF ), [Date] <= MAX ( 'Table'[EOmonth] ) ),
        [Location],
        [Last date],
        "avg", AVERAGE ( CQF[Value] )
    )
VAR _B =
    TOPN ( 1, _a, [Last date], DESC )
RETURN
    MAXX ( _B, [avg] )

     

    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

      Hi v-zhangti 

       

      Thanks for taking time to help me with my query! Appreciate it so much

       

      I noticed you have used a additional table called 'table' where you map location with the End of month dates. 

       

      How do I do that in my table structure? I have over 80 locations and the end of the month will need to be updated every month as this data will be updated monthly.

       

      In my table structure I have a Dim-date (date table), CQF table (where I have the values and dates)

       

      What I basically need is to get from the data on the left to the graph on the right. 🙂

       

      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        Hi, Anonymous 

         

        I readjusted my date sheet and it should look the same as yours this time.

        Dim-date = CALENDAR(DATE(2020,2,1),DATE(2021,11,30))
        Month End = 
CALCULATE (
    ENDOFMONTH ( 'Dim-date'[Date] ),
    FILTER (
        ALL ( 'Dim-date' ),
        MONTH ( [Date] ) = EARLIER ( 'Dim-date'[Date].[MonthNo] )&&YEAR([Date])=EARLIER('Dim-date'[Date].[Year])
    )
)

        Use the calculation column above to calculate the last day of each month.

         

        Measure = 
VAR _a =
    SUMMARIZE (
        FILTER ( ALL ( CQF ), [Date] <= MAX ( 'Dim-date'[Month End] ) ),
        [Location],
        [Last date],
        "avg", AVERAGE ( CQF[Value] )
    )
VAR _B =
    TOPN ( 1, _a, [Last date], DESC )
RETURN
    MAXX ( _B, [avg] )

         

        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