topic Re: DAX Help - Mandays Calculation in DAX Commands and Tips

DAX Help - Mandays Calculation

Madhans — Fri, 04 Aug 2023 05:02:40 GMT

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.

Re: DAX Help - Mandays Calculation

Jihwan_Kim — Fri, 04 Aug 2023 05:24:09 GMT

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] ) ) ) )

Re: DAX Help - Mandays Calculation

Madhans — Sun, 06 Aug 2023 08:14:50 GMT

Jihwan_Kim Thankyou so much . It worked!

Re: DAX Help - Mandays Calculation

Madhans — Mon, 07 Aug 2023 09:50:40 GMT

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.

Re: DAX Help - Mandays Calculation

Jihwan_Kim — Tue, 08 Aug 2023 03:09:49 GMT

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] ) ) ) ) ) )