Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PaulBI
Frequent Visitor

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 find the average number of weekend hours.

I've tried a few different approaches, but below is the most recent.  It works up to the point of adding the PreviousMonth portion.  Once I add that I only get blank.  Any help would be appreciated.

 

Test = calculate(divide(sum(Overtime[Activity_Hours]),DISTINCTCOUNT('Overtime'[Activity_Date]),0),WEEKDAY(Overtime[Activity_Date],2)>5,PREVIOUSMONTH(Overtime[Activity_Date]))
1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

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):
Capture.PNG

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!



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

8 REPLIES 8
Stachu
Community Champion
Community Champion

how many months back do you want to average? do you want to average per month or just to have average umber of hours per weekend day?

 

having some sample rows from the overtime table in copiable format would help me work faster as well, like this

Column1 Column2
A 1
B 2.5


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

PaulBI
Frequent Visitor

Right now I'm only looking for the month before the latest date.  I have a monthly average with no months specifically filtered that I can plot and break out by month, but when I try to only pick the last month for use in another measure I run into an issue.  The data refreshes once per week so typically it will be last month in relation to today with a few expections.  Thanks!

 

This is a sample of the data

 

Activity_DateActivity_Hours
Sunday, June 30, 201912
Sunday, June 30, 20198
Friday, June 28, 20198
Thursday, June 27, 201912
Tuesday, June 25, 201912
Sunday, July 21, 201912
Saturday, July 20, 20198
Saturday, July 06, 20198
Friday, July 05, 201912
Thursday, July 04, 201912
Wednesday, July 03, 201912
Wednesday, July 03, 20198
Tuesday, July 02, 20198
Monday, July 01, 201912
Sunday, June 30, 201912
Saturday, June 29, 201912
Friday, June 28, 20198
Thursday, June 27, 20198
Thursday, June 27, 201912
Wednesday, June 26, 201912
Tuesday, June 25, 201912
Monday, June 24, 20198
Tuesday, May 28, 20198
Monday, May 27, 201912
Sunday, May 26, 201912
Sunday, May 26, 20198
Saturday, May 25, 20198
Stachu
Community Champion
Community Champion

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):
Capture.PNG

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!



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

PaulBI
Frequent Visitor

@Stachu 

 

It fixed a lot of it but there is still one small part.  Thank you for your help so far.  When I create a table visual it shows that it works.  Now I'm using this information as an input into a projection.  I'm adding a column in the date table to project based on last month's numbers, what the rest of the month looks like but when I use the measures for that, I get numbers that don't match the table visualization.  I verified independantly in excel that the visual values are correct.  

 

Forecasted = if('Date'[Dateswithdata]=false,if('Date'[Weekend?]=true,[Last Month Average Monthly Weekend Day Hours],[Last Month Average Monthly Weekday Hours]),BLANK())

 

Correct Table Visual:

Capture.JPG

Column (forecasted) with incorrect numbers, using the same measure:

Capture2.JPG

 

Thank you for the information so far.  Also, for my own knowledge, can you explain why I needed to use the keepfilters function in the measure?

Stachu
Community Champion
Community Champion

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!



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

PaulBI
Frequent Visitor

@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())

Capture3.JPG

PaulBI
Frequent Visitor

@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
Community Champion
Community Champion

glad you got it working Smiley Happy



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.