Help with Measure

[Anonymous]

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!

[v-zhangti]

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]

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]

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]

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]

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.

[Anonymous]

Hi @v-zhangti,

The table seems to be working fine. However, the line graph does not seem to update with filter selection.

Would you be help (hopefully for the last time!)

Again, appreciate your help so much!

[v-zhangti · Accepted solution]

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]

Hi @v-zhangti ,

Thank you so much for your patience and time!

Finally works just fine!

thanks 🙂

[Anonymous]

@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...

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]

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

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

[Anonymous]

hi @amitchandak ,

I'm a bit cconfused now 🙂

Are you able to be a bit more clearer?

Thanks so much for your time again

[amitchandak]

@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.