cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jignaski18
Helper II
Helper II

Grouped Averages by Last Modified Date for Current Month

I am trying to do a few things here with my data.

 

The first is to show the last value modified for the current month when a filter is selected. This I have figured out.

 

The second (my issue), is when a filter is not selected, to average the last modified values across all districts for the current month. I need it to average the dates in yellow. It is currently averaging the values in orange.

 

Book1#Sheet1!E3_K15.jpg

 

VAR MD =
    CALCULATE(
        MAX('DM Labor'[Modified]),ALLEXCEPT('DM Labor','DM Labor'[District]))
VAR LF = 
CALCULATE(
    AVERAGE('DM Labor'[Labor/Fleet]),FILTER('DM Labor','DM Labor'[Modified]=MD),FILTER('DM Labor',MONTH('DM Labor'[Weekof])=MONTH(LASTDATE(Dates[Date]))))/1000
VAR LF1=  
    CALCULATE(AVERAGE('DM Labor'[Labor/Fleet]),
    FILTER('DM Labor',MONTH('DM Labor'[Weekof])=MONTH(LASTDATE(Dates[Date]))))/1000
VAR LFCM = CALCULATE(
    IF(ISFILTERED('Maintenance Plants'[District]),LF,LF1))
RETURN
IF(ISBLANK(LFCM),"NA",LFCM)
1 ACCEPTED SOLUTION

This was my last soul to get the unique value to show on a card.

VAR MD =
     CALCULATE(
        MAX('DM Labor'[Modified]),FILTER(ALL('DM Labor'),YEAR('DM Labor'[Modified])=YEAR(TODAY())&&MONTH('DM Labor'[Modified])=MONTH(TODAY())))
VAR Tab =
    ADDCOLUMNS(SUMMARIZE(
        ALL('DM Labor'),
        'DM Labor'[District],
        "last date",
        MAX('DM Labor'[Modified])),
        "result",
        CALCULATE(
            AVERAGE('DM Labor'[Labor/Fleet]),
            FILTER(
                ALL('DM Labor'),
                'DM Labor'[District]=EARLIER('DM Labor'[District])&&
                'DM Labor'[Modified]=EARLIER([last date])&&MONTH('DM Labor'[Weekof])=MONTH(TODAY()))))
VAR LFCM = AVERAGEX(Tab,[result])/1000
Return
IF(ISBLANK(LFCM),"NA",LFCM)

View solution in original post

2 REPLIES 2
v-alq-msft
Community Support
Community Support

Hi, @jignaski18 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

f1.png

 

Slicer:

f2.png

 

You may create two measures as below.

Last Modified Date = 
var _maxdate = 
CALCULATE(
    MAX('Table'[Modified Date]),
    FILTER(
        ALL('Table'),
        YEAR([Modified Date])=YEAR(TODAY())&&
        MONTH([Modified Date])=MONTH(TODAY())
    )
)
return
IF(
    ISFILTERED(Slicer[IsFiltered]),
    _maxdate
)

Avg = 
var _maxdate = 
CALCULATE(
    MAX('Table'[Modified Date]),
    FILTER(
        ALL('Table'),
        YEAR([Modified Date])=YEAR(TODAY())&&
        MONTH([Modified Date])=MONTH(TODAY())
    )
)
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        ALL('Table'),
        'Table'[District],
        "LastDate",
        MAX('Table'[Modified Date])
    ),
    "Result",
    CALCULATE(
        SUM('Table'[Cost]),
        FILTER(
            ALL('Table'),
            'Table'[District]=EARLIER('Table'[District])&&
            'Table'[Modified Date]=EARLIER([LastDate])
        )
    )
)
return
IF(
    NOT( ISFILTERED(Slicer[IsFiltered]) ),
    AVERAGEX(
        FILTER(
            tab,
            [LastDate]=_maxdate
        ),
        [Result]
    )   
)

 

Result:

f3.png

 

f4.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This was my last soul to get the unique value to show on a card.

VAR MD =
     CALCULATE(
        MAX('DM Labor'[Modified]),FILTER(ALL('DM Labor'),YEAR('DM Labor'[Modified])=YEAR(TODAY())&&MONTH('DM Labor'[Modified])=MONTH(TODAY())))
VAR Tab =
    ADDCOLUMNS(SUMMARIZE(
        ALL('DM Labor'),
        'DM Labor'[District],
        "last date",
        MAX('DM Labor'[Modified])),
        "result",
        CALCULATE(
            AVERAGE('DM Labor'[Labor/Fleet]),
            FILTER(
                ALL('DM Labor'),
                'DM Labor'[District]=EARLIER('DM Labor'[District])&&
                'DM Labor'[Modified]=EARLIER([last date])&&MONTH('DM Labor'[Weekof])=MONTH(TODAY()))))
VAR LFCM = AVERAGEX(Tab,[result])/1000
Return
IF(ISBLANK(LFCM),"NA",LFCM)

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.