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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate days from Date slicer

Hello,

I have below data in my excel

paragchapre1_0-1620299726687.png

 



I am using From date as a date slicer and calculating SIck days and reporting period.
I have only record from 1st May to 6 May 2021. If I provide a date outside this date range, then I don't get the correct reporting days.

paragchapre1_1-1620299726686.png

 


This is my measure to calculate reporting days.

BI_reportingperiod =

VAR FirstReportingDate = MIN( EmployeeSick[From date])
VAR LastReportingDate = MAX( EmployeeSick[From date])
RETURN

DATEDIFF ( FirstReportingDate, LastReportingDate+1, DAY)


Can anybody help me here?


Regards,
Parag Chapre

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

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a date table

2. Create a relationship between date table and employeesicks table base on date field

3. Create a measure as below

BI_reportingperiod = 
VAR FirstReportingDate = MIN( 'Date'[Date])
VAR LastReportingDate = MAX( 'Date'[Date])
RETURN
DATEDIFF ( FirstReportingDate, LastReportingDate+1, DAY)

yingyinr_0-1620641326881.png

Best Regards

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

4 REPLIES 4
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a date table

2. Create a relationship between date table and employeesicks table base on date field

3. Create a measure as below

BI_reportingperiod = 
VAR FirstReportingDate = MIN( 'Date'[Date])
VAR LastReportingDate = MAX( 'Date'[Date])
RETURN
DATEDIFF ( FirstReportingDate, LastReportingDate+1, DAY)

yingyinr_0-1620641326881.png

Best Regards

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

@Anonymous 

 

Please familiarize yourself with how time intelligence in Power BI works. Once you know the basic rules, you'll know immediately why you get what you get. You can start here: Time Intelligence in Power BI Desktop - SQLBI

 

Incidentally, you get the above result because you're not using a true date table in your model. Please bear in mind that fact tables' attributes should never be used to slice and dice by; in fact, fact tables MUST always be hidden. One exception to this rule is attributes that constitute the so-called degenerate dimension (but you don't have it). Only dimensions must be used for slicing and dicing. Try to deviate from this rule and you'll be sorry sooner rather than later.

amitchandak
Super User
Super User

@Anonymous , Try if this can work

 

VAR FirstReportingDate = MINX(allselected(EmployeeSick) ,EmployeeSick[From date])
VAR LastReportingDate = MAXX(allselected(EmployeeSick) , EmployeeSick[From date])
RETURN

DATEDIFF ( FirstReportingDate, LastReportingDate+1, DAY)

Anonymous
Not applicable

No. it's not working. Is there any workaround?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.