cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

Date filtering in report measure

Hello. In a table, I am trying to show the number of hours worked for each employee on the last day that the employee worked. For example, if employee Y worked 6 hours on 02/06/2020 and employee X worked 8 hours on 02/07/2020, I would like to show those hours within the table. 

 

My current data model includes an employee table listing names and employee IDs and a fact table that has a date column, hours worked,. and an employee ID. I am currently creating a one way relationship between the employee table and fact table. 

 

How can I accomplish this visualization using DAX? 

 

Thank you.

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

I just create one sample PBIX file based on your case description, please find the details in this file.

calculate working hours of last work day.JPG

If the above measure formula is not applicable in your scenario, please provide the related table structure and sample data.

 

Best Regards

Rena

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

View solution in original post

8 REPLIES 8
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

I just create one sample PBIX file based on your case description, please find the details in this file.

calculate working hours of last work day.JPG

If the above measure formula is not applicable in your scenario, please provide the related table structure and sample data.

 

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Exactly what I was looking for. Thank you so much!

Anonymous
Not applicable

Please check if below measure could help you.

Measure  =
CALCULATE (
    SUM ( factSales[Amount] ),
    FILTER (
        ALL ( factSales ),
        factSales[Date] >= MAX ( Contract[active dates] )
            && factSales[Date] <= MAX ( Contract[end dates] )
    )
)
Anonymous
Not applicable

What is contract reffering to?

VasTg
Memorable Member
Memorable Member

@Anonymous 

 

Assuming you don't have date/calendar table, I would recommend you to create one. But it is not necessary to use the below DAX.

 

Measure = CALCULATE(SUM('Fact'[Hours]),FILTER('Fact','Fact'[Date]=MAX('Fact'[Date])))

 

Left table is the desired output with measure.. The right two tables are input data..

 

image.png 

 

If it helps ,mark it as a solution

Kudos are nice too

Connect on LinkedIn
Anonymous
Not applicable

Thank you for the reply. I do have this schema and have attempted the DAX formula, but did not get the result. Would there be any difference in the row context if the formula was instead:

CALCULATE(SUMX(VALUES(EMPLOYEE[NAME]),SUM(FACT[HOURS])),FILTER(FACT,FACT[DATE] = MAX(DATETABLE[DATE])))

 

@Anonymous 

 

Why do you need sumx? Don't you have the relationship defined between employee and fact? Here is what i have for the model.

 

Considering date dimension..

CALCULATE(SUM('Fact'[Hours]),FILTER('Fact','Fact'[Date]=MAX('Date'[Date])))

 

image.png

 

Here is my Date table definition.

 

Date = CALENDAR(MIN('Fact'[Date]),MAX('Fact'[Date]))

 

If it helps, mark it as a solution

Kudos are nice too

Connect on LinkedIn
Anonymous
Not applicable

Power BI Example.PNG

 

Note that gerald is showing 0 hours on the left hand table, but I would like to capture Gerald's hours for Feb 6th.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors