cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasWeppler
Advocate I
Advocate I

Multiple date filters in same table

I need a table which shows the % sick leave for employess over 12 and 3 months, so the two numbers easily can be compared.

For the 12 months I just added a relative date for the last 12 months. 

ThomasWeppler_0-1616597094049.png

To add the three months filter I wrote it in Dax and added the same calculation, but with a dateinperiode filter. 

Here is the Dax

 

Fraværsprocent 3 =
DIVIDE(
CALCULATE (
COUNTROWS ( 'workplanentry' ),
'workplanentry'[title] = "Sygedag"
|| 'workplanentry'[title] = "Barn 1. sygedag"
|| 'workplanentry'[title] = "langtidssygemeldt"
|| 'workplanentry'[title] = "Coronasyg"
|| 'workplanentry'[title] = "off sick", DATESINPERIOD('workplanentry'[date], TODAY(), -3, MONTH)
),
CALCULATE (
COUNTROWS ( 'workplanentry' ),
'workplanentry'[title] = "Sygedag"
|| 'workplanentry'[title] = "Barn 1. sygedag"
|| 'workplanentry'[title] = "Fraværsdag"
|| 'workplanentry'[title] = "Omsorgsdag"
|| 'workplanentry'[title] = "Arbejdsdag"
|| 'workplanentry'[title] = "Skole/Kursus"
|| 'workplanentry'[title] = "Afspadsering"
|| 'workplanentry'[title] = "langtidssygemeldt"
|| 'workplanentry'[title] = "Coronasyg"
|| 'workplanentry'[title] = "off sick"
|| 'workplanentry'[title] = "dødsfald"
|| 'workplanentry'[title] = "Corona produktionsstop"
|| 'workplanentry'[title] = "Workday", DATESINPERIOD('workplanentry'[date], TODAY(), -3, MONTH))
)

After I wanted to check if it worked as intended, so I changed the first filter to 3 months thinking that the 2 numbers would be the same, since I would apply the same filter to both calculations.
The number was however different.

So here is my question. Can anyone tell me why the dateinperiod and relative date works diffrently. Can I change something in my Dax function to get the same numbers or would it be better to build the calculation in an entirely different ways?

All help is appreciated. 🙂
1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@ThomasWeppler , relative date filter the dates in table. Means 3 months of data is only available.

 

Say you filter for a date and use dateinperiod for 3 month with date table , Then you will get data rolling for 3 month for that date.

 

If you more than one date selected dateinperiod  will give 3 months for all the dates

 

Also, check this video when wanting to select one date and show a trend https://www.youtube.com/watch?v=duMSovyosXE



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
amitchandak
Super User IV
Super User IV

@ThomasWeppler , relative date filter the dates in table. Means 3 months of data is only available.

 

Say you filter for a date and use dateinperiod for 3 month with date table , Then you will get data rolling for 3 month for that date.

 

If you more than one date selected dateinperiod  will give 3 months for all the dates

 

Also, check this video when wanting to select one date and show a trend https://www.youtube.com/watch?v=duMSovyosXE



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors