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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
whatlje1
Regular Visitor

Dax Calculations

I am trying to do some Calculations to figure out "Wage" based on certain Criteria. Here is what I have:

SUMX(
    'Federal Holidays',
    SWITCH(
        TRUE(),
    'Federal Holidays'[Holiday] = "New Year's Day 3 Days After" || 'Federal Holidays'[Holiday] = "New Year's Day 3 Days Before",
            IFERROR(
                CALCULATE(
                Round( [_hours],0) * [_estimatedWageAverage]
                ,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}
                ,Dates[month index] > -16
                ,Dates[month index] < -1
                ),0
            )  
    ,'Federal Holidays'[Holiday] <> "New Year's Day 3 Days After" || 'Federal Holidays'[Holiday] <> "New Year's Day 3 Days Before",
            IFERROR(
                CALCULATE(
                Round( [_hours],0) * [_estimatedWageAverage]
                ,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}
                ,Dates[year index] = 0
                ),0
            )
    )
)
What I need to remove is the SUMX part. I cannot figout another way to do this calculation.
 
What I am looking to do in one Formula, is to do something like this(I did Merge my Federal Holiday table to my Main Table):
_estimatedSavingsCurrentYear2 =
IF('PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] = "New Year's Day 3 Days After" || 'PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] = "New Year's Day 3 Days Before",
            IFERROR(
                CALCULATE(
                Round( [_hours],0) * [_estimatedWageAverage]
                ,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}
                ,Dates[month index] > -16
                ,Dates[month index] < -1
                ),0
            ),
            IFERROR(
                CALCULATE(
                Round( [_hours],0) * [_estimatedWageAverage]
                ,'PTO/VTO/OTAppendNICEtoCalabrio'[_exception] IN {"VTO" , "Voluntary Time Off (VTO)"}
                ,Dates[year index] = 0
                ),0
            )
    )
 
 
'PTO/VTO/OTAppendNICEtoCalabrio'[Holiday Name] is a Column within the table.
 
 
1 REPLY 1
JosefPrakljacic
Solution Sage
Solution Sage

Hey @whatlje1 ,

 

It's no disgrace to publish sample data. Especially if you want help with DAX formulas and the helping persons have to test their formulas themselves in order not to give any wrong answer or to guess blindly.

 

So may I ask you, if you ask for help from others, to also fulfill your contribution and provide sample data?

 

BR,

Josef

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors