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!

Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

regarding KEEPFILTERS - as we use Calendar[Date] as a second argument for CALCULATE the filter context of that column is overwritten to show only weekends - all the weekends. Now we don't won't to show all of them, only the ones that match current filter context for month - that's why I added KEEPFILTERS
you can read more on CALCULATE here
https://www.sqlbi.com/articles/filter-arguments-in-calculate/

 

the filter context of Date in calculated column is the value in the row, so it calculates in reference to a single date. Other than that adding fact data to a dimension table is not a very elegant solution. Do you need forecast in the calculated column, or could it be in the measure or calculated table? How do you need to use it later? the values should be the same (avg for latest month with actual data) for all the future periods, correct?

 

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 

 

Below is the graphic I'm trying to create, where the projected is using last month's average # of hours per weekday and weekend to project to the end of the month.  Right now the projection is being calculated by the measure 'Projected" (below) which uses the calculated column in the dates table in the picture in my earlier post (Date[Forecast]) but like I said, it is not actually relaying the true previous month average.  All the help so far is really appreciated.  If you have any insight into how better to do this would be appreciated too.  Thanks!

 

Projected = 
if(SELECTEDVALUE('Date'[Dateswithdata])=FALSE(),calculate(sum(Overtime[Activity_Hours])+(sum('Date'[Forecasted])),DATESMTD('Date'[Date]),year('Date'[Date])>=year(TODAY()),month('Date'[Date])=month(today())),BLANK())

  • 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: