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.

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

      Hi v-zhangti again,

       

      The measure values are giving me wrong values.

       

      My guess is  - this is because the CQF table has more than 1 location

       

      Could I please get some help with tweaking the measure?

       

      Arrgh so close!!

       

      Thanks so much for your time and assistance!

       

       

       

       

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

        Hi, Anonymous 

         

        Except measure, the others remain unchanged.

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

        Add a condition about filtering location in measure.

         

        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.