IF BLANK RETURN AVERAGE OF PAST 3 WEEKS

[HYEasterly]

Let me start with saying I have read many posts that are similar to my question... and I have attempted many of those solutions.  I have not gotten the result I want... so here I am asking for help.

Goal:  IF the visual returns a blank for the Total Labor Cost THEN RETURN the average of the prior 3 weeks Total Labor Cost.  (We get revenue numbers in real time... but labor costs  have to wait on payroll to be ran the following week.  We want an average of the labor costs to show until the actual labor cost is provided.)

This is a snip of my visual.  Notice how May 18 is blank in the Labor column... 

My data is very complex and has multiple tables.  I have condensed it down to the tables and fields I think I need to use.

Table Name:	Labor Data
Field	Type
Period End Date	Data
Total Labor Cost	DAX
Department Number	Data
Wages	Data
Taxes	Data
Benefits	Data
Prior Week 1	DAX
Prior Week 2	DAX
Prior Week 2	DAX
Table Name:	Revenue Data
Field	Type
Period End Date	Data
Department Number	Data
Revenue	Data
Table Name:	Period Ending
Field	Type
Period End Date	Data
Prior Week 1	Data
Prior Week 2	Data
Prior Week 2	Data
Table Name:	Department Data
Field	Type
Department Number	Data
Department Name	Data

The Prior Week fields were added while trying to get this to work.    

How do you suggest I accomplish getting it to return the average of the prior 3 weeks labor cost if there is no labor cost for a specific week?

Thank you in advance!

[v-hjannapu]

Hi @HYEasterly,

Thank you for reaching out to the Microsoft fabric community forum.
I would also take a moment to thank @pankajnamekar25 , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I created your scenario using simplified sample data. I manually added the tables (Labor Data, Revenue Data, and a Calendar table), established the necessary relationships (based on Period End Date), and then implemented the DAX measure to return the average of the prior 3 weeks' labour cost when the current week is blank. I visualized the result using a Matrix visual, and it returned the expected values.

Please find the attached pbix file and screenshot for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Harshitha.
Community support Team.

[HYEasterly]

@v-hjannapu @pankajnamekar25 

Thank you both for your answers.  

My data is broken down by department and employee.  My visual also has a slicer so the user can look at certain departments.  The simplified version you provided doesn't account for either of these.  It returns the 3 largest paychecks from the last 3 weeks not the entire payroll for the last 3 weeks.

I'm still working with it to try to get my data.

[v-hjannapu · Accepted solution]

Hi @HYEasterly,

I have  created a solution that addresses your requirements, including employee-level data, department slicer, and averaging the prior 3 weeks’ labor costs when the current week is blank.

create the following DAX Measures to ensure accurate results:

Total Labor Cost = 
SUM('Labor Data'[Labor Cost])

Avg Labor Last 3 Weeks = 
VAR CurrentPeriod = MAX('Period Table'[Period End Date])
VAR Department = SELECTEDVALUE('Labor Data'[Department Number])

VAR PreviousPeriods =
    TOPN(
        3,
        FILTER(
            ALL('Period Table'),
            'Period Table'[Period End Date] < CurrentPeriod
        ),
        'Period Table'[Period End Date], DESC
    )

VAR Result =
    AVERAGEX(
        PreviousPeriods,
        CALCULATE(
            [Total Labor Cost],
            'Period Table'[Period End Date] = EARLIER('Period Table'[Period End Date]),
            'Labor Data'[Department Number] = Department
        )
    )

RETURN Result

Labor with Fallback = 
VAR Actual = [Total Labor Cost]
RETURN IF(
    ISBLANK(Actual),
    [Avg Labor Last 3 Weeks],
    Actual
)

I was able to achieve the expected output as per your requirement.
Please refer to the screenshot and attached pbix file below showing the correct results:

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Harshitha.
Community support Team.

[Ashish_Excel]

Hi,

This pattern should work

Measure = if(isblank([Total labour cost]),calculate([total labour cost],datesbetween(calendar[date],min(calendar[date])-21,min(calendar[date])-1)),[total labour cost])

[pankajnamekar25]

Hello @HYEasterly 

Try this

Total Labor Cost - Adjusted :=

VAR CurrentWeek = SELECTEDVALUE('Labor Data'[Period End Date])

VAR CurrentLabor = SELECTEDVALUE('Labor Data'[Total Labor Cost])

VAR PreviousWeeks =

    CALCULATETABLE (

        VALUES('Labor Data'[Total Labor Cost]),

        FILTER (

            ALL('Labor Data'),

            'Labor Data'[Period End Date] < CurrentWeek

        ),

        TOPN(3,

            FILTER (

                ALL('Labor Data'),

                'Labor Data'[Period End Date] < CurrentWeek &&

                NOT ISBLANK('Labor Data'[Total Labor Cost])

            ),

            'Labor Data'[Period End Date], DESC

        )

    )

VAR AvgLast3 =

    AVERAGEX(PreviousWeeks, [Total Labor Cost])

RETURN

    IF (

        ISBLANK(CurrentLabor),

        AvgLast3,

        CurrentLabor

    )

You could alternatively use those columns in the measure instead

Total Labor Cost - Adjusted :=

VAR CurrentLabor = [Total Labor Cost]

VAR Prior1 = [Prior Week 1]

VAR Prior2 = [Prior Week 2]

VAR Prior3 = [Prior Week 3]

VAR AvgPrior3 = AVERAGEX(

    { Prior1, Prior2, Prior3 },

    [Value]

)

RETURN

    IF (

        ISBLANK(CurrentLabor),

        AvgPrior3,

        CurrentLabor

    )

But this is less dynamic  using the first method with date filtering is more scalable.

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.