Forum Discussion

Madhans's avatar
Madhans
Frequent Visitor
3 years ago
Solved

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.
@dax @help
@Dax help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

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

     

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

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

     

  • Madhans's avatar
    Madhans
    Frequent Visitor
    3 years ago

    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.

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      3 years ago

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