cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

DAX Help - Mandays Calculation

I have one fact table containing
Employee Clm, Joining-date Clm, Relieving-date Clm

And one calendar table.

Need to have the number of days in the particular period as below attached table visual for the year 2022

Kind note- My slicer is from calendar table & Relieving date blank means still the employee is in the company
And i want the days between column to be dynamic.
1 ACCEPTED SOLUTION
Super User

Hi,

Please check the below picture and the attached pbix file.

``````expected outcome measure: =
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Date] >= MAX ( Employee[Joined Date] )
&& IF (
NOT ISBLANK ( MAX ( Employee[Relieving Date] ) ),
'Calendar'[Date] <= MAX ( Employee[Relieving Date] ),
MAX ( 'Calendar'[Date] )
)
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

4 REPLIES 4
Frequent Visitor

In my table visual, I've noticed that the total of the measure is not appearing.This is concerning to me, as I intend to utilize this total value in a card visualization as well.
@Jihwan_Kim I would greatly appreciate it if you could spare some time to help me troubleshoot and rectify this situation.

And i am also going to calculate Last 12 months total sum for the above measure. Kinldy advise me whther the attached dax will work or not

CALCULATE (
Table Name[expected outcome measure:],
DATESINPERIOD (
'Calendar'[Date Main],
LASTDATE ( 'Calendar'[Date Main] ),
-12,
MONTH
)
)

Note - My my employee table and calendar tabale does not have any relationship.

Super User

Hi,

Please try the below in order to see the total row in the table visualization.

``````expected outcome measure: =
SUMX (
VALUES ( Employee[Employee] ),
CALCULATE (
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Date] >= MAX ( Employee[Joined Date] )
&& IF (
NOT ISBLANK ( MAX ( Employee[Relieving Date] ) ),
'Calendar'[Date] <= MAX ( Employee[Relieving Date] ),
MAX ( 'Calendar'[Date] )
)
)
)
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Super User

Hi,

Please check the below picture and the attached pbix file.

``````expected outcome measure: =
COUNTROWS (
FILTER (
'Calendar',
'Calendar'[Date] >= MAX ( Employee[Joined Date] )
&& IF (
NOT ISBLANK ( MAX ( Employee[Relieving Date] ) ),
'Calendar'[Date] <= MAX ( Employee[Relieving Date] ),
MAX ( 'Calendar'[Date] )
)
)
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

@Jihwan_Kim  Thankyou so much . It worked!

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors