Average Week to date (WTD) ignoring blanks and zeros (dax Measure)

[Mramono]

Hello Friend

I have tried everything i know by know to try come up with the Measure that can calculate Week to date (WTD) AVERAGE. Unfortunately all the help i found in my research does not work. I don't know why. I hope someone can take me out of this misery.

I provide a link to my data table

https://docs.google.com/spreadsheets/d/1g_EMyXXKf8sb_xRQNBRcxseietRQ5YRC/edit?usp=drive_link&ouid=10...

Basically I need Dynamic WEEK TO DATE (WTD) average based on date slicer for "Prod Quality (%)" in my data table (ProdData2). I have managed to get dynamic WTD sum for the "Prod Tonnes"

I will appreciate help Please!!

Kind regards

mramono

[Irwan]

hello @Mramono 

i assumed WTD is a running sum for a week.

please check if this accomodate your need.

1. create a new table for calendar date.

2. create a relationship between calendar and your fact table.

3. create a new measure with following DAX to calculate WTD for same week and year.

WTD =
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Production Date]<=MAX('Calendar'[Date])&&
        WEEKNUM('Table'[Production Date])=SELECTEDVALUE('Calendar'[Week])&&
        YEAR('Table'[Production Date])=SELECTEDVALUE('Calendar'[Year])
    ),
    'Table'[Prod Tonnes]
)

Hope this will help.

Thank you.

[Mramono]

@Irwan thank you for the running sum, however i am mostly struggling with Running AVERAGE. Basically Running sum divided by number of rows in a running week (where the rows are non zeros and non blank)

I guess we need a dax that can COUNT ROWS that are non zero and non blank for the running week.

[Irwan]

hello @Mramono 

if you need running average, you can change SUMX into AVERAGEX.

if you need to exclude non-blank and zero value, then you can add more filter into the DAX.

Thank you.

[v-dineshya]

Hi @Mramono ,

Thank you for reaching out to the Microsoft Community Forum.

As you mentioned in your previous post, you want a DAX measure that can COUNT ROWS that are non zero and non blank for the running week.

Please refer below DAX measure.

WTD Avg Quality =

VAR CurrentDate = MAX ( 'DateTable'[Date] )

VAR CurrentWeek = MAX ( 'DateTable'[YearWeek] )

VAR WTD_Table =

    FILTER (

        ALL ( 'DateTable' ),

        'DateTable'[YearWeek] = CurrentWeek &&

        'DateTable'[Date] <= CurrentDate

    )

VAR WTD_Data =

    FILTER (

        ProdData2,

        ProdData2[Production Date] IN SELECTCOLUMNS ( WTD_Table, "Date", 'DateTable'[Date] ) &&

        NOT ISBLANK ( ProdData2[Prod Quality (%)]) &&

        ProdData2[Prod Quality (%)] <> 0

    )

VAR Sum_Quality = SUMX ( WTD_Data, ProdData2[Prod Quality (%)] )

VAR Count_Quality = COUNTROWS ( WTD_Data )

RETURN

DIVIDE ( Sum_Quality, Count_Quality )

Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[Mramono]

Hi @v-dineshya thank you very much for coming in to assist. Unfortunately i don't quite get the right answer. E.g. on the first week of the year in the my data, when i just use excel to get average for the first week the answer is 35.83 but in power bi on the script you graciously provided the answer is 33.02. I basically used a date slicer and chose 5th Jan, I assumed it will give me the average from the begining of the year to the 5th Jan.

Is it possible to make a separate measure that only count rows excluding zeros and blanks for Week to date?

[v-dineshya · Accepted solution]

Hi @Mramono ,

Thank you for the update.  I have created two measures WTD_Avg_Quality and  WTD_NonZeroCount. Please refer below 

1.

WTD_Avg_Quality =

VAR CurrentDate = MAX ( 'Date'[Date] )

VAR StartOfWeek = CurrentDate - ( WEEKDAY ( CurrentDate, 2 ) - 1 )

VAR WTD_Data =

    FILTER (

        ALL ( ProdData2 ),

        ProdData2[Production Date] >= StartOfWeek

            && ProdData2[Production Date] <= CurrentDate

            && NOT ISBLANK ( ProdData2[Prod Quality (%)] )

            && ProdData2[Prod Quality (%)] <> 0

    )

RETURN

AVERAGEX ( WTD_Data, ProdData2[Prod Quality (%)] )

2. 

WTD_NonZeroCount =

VAR CurrentDate = MAX ( 'Date'[Date] )

VAR StartOfWeek = CurrentDate - ( WEEKDAY ( CurrentDate, 2 ) - 1 )

VAR WTD_Data =

    FILTER (

        ALL ( ProdData2 ),

        ProdData2[Production Date] >= StartOfWeek

            && ProdData2[Production Date] <= CurrentDate

            && NOT ISBLANK ( ProdData2[Prod Quality (%)] )

            && ProdData2[Prod Quality (%)] <> 0

    )

RETURN

COUNTROWS ( WTD_Data )

Please refer below output snap and attached PBIX file.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[Mramono]

Thank you so much @v-dineshya this works wonders, your dax code is doing exactly what i need.

Very much oblidged!!!

mramono