show "rest data" older then 3 months dynamically in matrix

[Anonymous]

Hi Guys,

I've been tinkering with Power BI for a little while now, and finally gotten my first assignment at work.

Unfortunately i can't wrap my head around a solution to the request i've gotten.

Success criteria:

1. Show the hour rate, based on hours worked/starting fee -it works, but open to suggestions.
2. Have a visual with quick overview of hours used in the last 3 months on different projects. -Properly needs a different ( and better) solution then filters
3. only show Hourly_fee in the totals and NOT under every month. -i've disabled word wraping a hidden the column for now

main issue:

if a project have been running for more then 3 months, i need to show the hours used before the start of the 3 month-period.

this is currently not possible, because i'm using a filter to show the last 3 months in my matrix.

This is a minimized replica of the data i'm working with

Example file here 

I hope one of you can give me a hint or two.

Thank you in advance.

[v-yetao1-msft]

Hi @Anonymous 

Has your problem been solved ? If it has been solved, provide your method for the problem then consider Accept it as the solution to help the other members find it more quickly.

Best Regards

Community Support Team _ Ailsa Tao

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

The data you provided is a bit complicated and not easy to create. I created a sample, you can refer to this method.

Original data :

(1)First get the maximum date and minimum date of the project .

(2)Use MONTH dax to return the number for date and compare the diff between max date and min date .IF the diff >3 ,then sum the hours before 3 months ago , or return 0 .

worked +3 months ago =

var max_date=CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Project]))

var min_date=CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Project]))

var sum_hours = CALCULATE(SUM('Table'[Hours]),FILTER(ALLEXCEPT('Table','Table'[Project]),'Table'[Date]<EOMONTH( CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Project])),-3)+1))

return

IF(MONTH(max_date)-MONTH(min_date)>3,sum_hours,0)

The effect is as shown:

You can use Matrix visual to display the result . Note :measure can only put in Values ,and then it will display in each column ,you can hide them just keep the total one .

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hi @v-yetao1-msft ,

I managed to get it to work with the help of your DAX.

I had to change the values to the appropiate data, but it seems to be working now.

Thank you for your help!

[v-yetao1-msft]

Hi @Anonymous 

(1) Only show Hourly_fee in the totals and NOT under every month ,you can turn off Word wrap in both Values and Column headers .

(2)You said that if the project has been running for more than 3 months , you need to show the hours used before the start of the 3 month-period . In your sample , what is your start date ?

For example , the project “extend service” has a min date  2021/02/02 , so you need sum the hours before 2021/02/02 ?

You can create a measure like:

Measure = CALCULATE(SUM(task[hours_worked]),FILTER(ALLEXCEPT(project,project[project_name]),SELECTEDVALUE(task[date_worked])<MIN(task[date_worked])))

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hello @v-yetao1-msft,

(1) i'm already doing this, and since you also suggested it, i'm gonna continue using this -Thank you

(2) You said that if the project has been running for more than 3 months , you need to show the hours used before the start of the 3 month-period . In your sample , what is your start date ?

Unfortunately i totally forgot to add the start date, but it would be project[start_date]

For example , the project “extend service” has a min date  2021/02/02 , so you need sum the hours before 2021/02/02 ?

 I'm sorry for not writing my wishes correctly.

A project can run for lets say 4 months.

my matrix (should) only shows the last 3 named months.

i'm hoping that it would be possible to:

have a column before the months with all hours worked before the shown 3 months.

Something similar to the picture below

i hope that makes sense and thank you!