Forum Discussion

PaulBI's avatar
PaulBI
Frequent Visitor
7 years ago
Solved

Problems getting previous month averages

I have one table (Overtime) with Activity_date and Activity_hours.  I have a date table (Date) which has a relationship between activity_date and the date column of the date table.  I am trying to fi...
Previous month
  • Stachu's avatar
    Stachu
    7 years ago

    I'd use the Date table to modify the date filter context
    so e.g. if this is your average:

    Avg = 
CALCULATE (
    DIVIDE (
        SUM ( Overtime[Activity_Hours] ),
        DISTINCTCOUNT ( 'Overtime'[Activity_Date] ),
        0
    ),
    KEEPFILTERS(WEEKDAY ( 'Calendar'[Date], 2 ) > 5)
)

    you can calculate previous month average like this:

    Avg Prev Month = 
CALCULATE(
    [Avg],
    PREVIOUSMONTH('Calendar'[Date])
    )

    which calculated the period in reference to the filter context in the Calendar table (here row determines specific month):

    you can notice that [Avg Prev Month] is empty on total - that's because there is no specific month reference

    Did I answer your question? Mark my post as a solution!

    Proud to be a Datanaut!

PaulBI's avatar
PaulBI
Frequent Visitor
7 years ago

Stachu 

Thank you for all the help.  I really appreciate it.

 

After a bunch of trial and error I think I figured out the overall solution.  I took the dax from the calculated column, shifted from a sum to a sumx and pasted all of it in the measure where I had the calculated column.  It looks like this:

 

Projected = 


if(SELECTEDVALUE('Date'[Dateswithdata])=FALSE(),calculate(sum(Overtime[Activity_Hours])+(sumx('Date',if('Date'[Dateswithdata]=false,if('Date'[Weekend?]=true,[Last Month Average Monthly Weekend Day Hours],[Last Month Average Monthly Weekday Hours]),BLANK()))),DATESMTD('Date'[Date]),year('Date'[Date])>=year(TODAY()),month('Date'[Date])=month(today())),BLANK())
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

glad you got it working :smileyhappy: