Max for each week then average of Month sales in DAX.

[Anonymous]

Hi Team,

Hope all are doing well!

I have my sales data with multiple dimensions available but need your help in solving below case:

I need to calculate max sales amount in a week then show the average at month level.E>g

Date         Sales

1/1/2022  200

1/2/2022  400

1/8/2022  600

1/10/2022 800

1/21/2022 500

Week 1 Max sale is 400

Week 2 Max Sale  is 800

Week 4 Max sale is 500

Output should be

Jan22 -(400+800+500)/3=566,66

Please help @tamerj1  @FreemanZ  @amitchandak  @Jihwan_Kim  @daXtreme  @johnt75 @Anonymous @v-kkf-msft 

[Anonymous]

Hi @tamerj1 ,

Thank you for promt response. I am connecting through direct query so we dont have any weekNo column. How to fetch weekNo as measure and then rewrite this query?

because Values function expecting a column value not measure.

[tamerj1]

@Anonymous 
You have to have the column. If you don't the it has to be created from the source. Not sure if a locally imported/created Date table can be connected to your Sales table but it is worth it to try (of course if you are allowed to create relationships)

[Anonymous]

Thank you for the suggestion @tamerj1 . Since its a direct query connection then relationship tab is not available.

I dont think backend team would add a column 😞 

I am able to get weekNo in a measure using  (WeeNum=WeeKNUM(Max(Table[Date]))
Is ther any way to pass this to get desired output?

Max sale at each week and monthy average of maximum data?

[tamerj1]

@Anonymous 
It doesn't have to be connected table but can you even create a local table or locally import one?

[Anonymous]

Hi @tamerj1  Since its a direct query(SAP-HANA) so adding a local table or any other data source is disable on the Power BI desktop.

[tamerj1 · Accepted solution]

@Anonymous 
This is the best I can do. Not sure if it satisfies your rquirement. Please refer to attached sample file.

Sales Amount = 
VAR CurrentWeekUm = [Week Number]
VAR T1 =
    GROUPBY ( 
        ADDCOLUMNS ( ALL ( 'Table' ), "@Week", WEEKNUM ( 'Table'[Date], 12 ) ),
        [@Week],
        "@Sales", MAXX ( CURRENTGROUP(), [Sales] )
    )
VAR Amount = SUM ( 'Table'[Sales] )
VAR Result1 = 
    AVERAGEX (
        FILTER ( T1, [@Week] = CurrentWeekUm ),
        [@Sales]
    )
VAR Result2 =
    AVERAGEX ( T1, [@Sales] )
RETURN 
    IF ( 
        NOT ISEMPTY ( 'Table' ),
        IF ( 
            HASONEVALUE ( 'Table'[Date] ),
            IF ( Amount = Result1, Result1 ),
            Result2
        ) 
    )

[FreemanZ]

Hi @Anonymous 

1) you would need to create a WeekNum column with this:

WeekNum = WEEKNUM([Date],2)

2) create a measure with this:

MonthlyAvgWeeklyMax =

AVERAGEX(

    VALUES(TableName[WeekNum]),  

    MAXX(

        VALUES(TableName[Date]),

        CALCULATE(SUM(TableName[Value]))

    )

)

3) Plot a visual with the Month field. 

I tried and it worked like this:

dataset:

[Anonymous]

Hi @FreemanZ 

Thank you for promt response. I am connecting through direct query so we dont have any weekNo column. How to fetch weekNo as measure and then rewrite this query?

because Values function expecting a column value not measure.

[tamerj1]

Hi @Anonymous 
Please try

Sales Amount =
AVERAGEX (
    VALUES ( 'Date'[Week] ),
    CALCULATE (
        MAXX ( VALUES ( 'Date'[Date] ), CALCULATE ( SUM ( Sales[Sales] ) ) )
    )
)

[Anonymous]

@tamerj1  Do you have any other approach to fix this?