Multiple date filters in same table

[ThomasWeppler]

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. 

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. 🙂

[amitchandak · Accepted solution]

@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