Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Madhans
Frequent Visitor

DAX Help - Mandays Calculation

Madhans_0-1691125172211.png

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
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

 

Jihwan_Kim_1-1691126582062.png

 

 

Jihwan_Kim_0-1691126569730.png

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

4 REPLIES 4
Madhans
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.

Madhans_1-1691401529640.png

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.

Hi,

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

Jihwan_Kim_0-1691464158111.png

 

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

 

Jihwan_Kim_1-1691126582062.png

 

 

Jihwan_Kim_0-1691126569730.png

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

@Jihwan_Kim  Thankyou so much . It worked!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors