MTD calculation with condition

[shirinshon]

Hello,

I'm trying to calcualte MTD Earned Hours depending on the country. I have a table called Earned Hours Report which has columns such as site_ref, Standard Earned Hours etc. For example I want to calculate Earned Hours only for USA: Earned Hours MTD USA = IF ('EH Daily Report'[site_ref] = "USA", TOTALMTD( SUM('EH Daily Report'[Standard earned hours]),DimDate[Date], BLANK()), but it's not recognizing site_ref and gives an error. Could you please suggest how I can fix it?

[rohit1991 · Accepted solution]

Use this corrected DAX formula:

Key Fix:

• Wrapped the condition 'EH Daily Report'[site_ref] = "USA" inside CALCULATE to apply it as a filter.

Earned Hours MTD USA = 
CALCULATE(
    TOTALMTD(SUM('EH Daily Report'[Standard earned hours]), DimDate[Date]),
    'EH Daily Report'[site_ref] = "USA"
)

[ToddChitt · Accepted solution]

Have you tried something like this to get just the USA component:

Earned Hours USA = CALCULATE (SUM ( [earnedHours] ), [Country] = "USA" )

Then using that in something like this:

MTD Earned Hours USA = TOTALMTD ( [Earned Hours USA], Dates[Date] )

[rohit1991 · Accepted solution]

Use this corrected DAX formula:

Key Fix:

• Wrapped the condition 'EH Daily Report'[site_ref] = "USA" inside CALCULATE to apply it as a filter.

Earned Hours MTD USA = 
CALCULATE(
    TOTALMTD(SUM('EH Daily Report'[Standard earned hours]), DimDate[Date]),
    'EH Daily Report'[site_ref] = "USA"
)

[ToddChitt]

Glad that corrected DAX works. But just know this: If you use that measure for, say, another country, it will still give you the USA results. Try this: Start a new, blan page. Add a Date slicer and select a date segment, like the current month. Create a simple Table visual and add the column

'EH Daily Report'[site_ref]' to it. Now add the [Earned Hours MTD USA] to the table.

It probably has the same value for every distinct value of [site_ref]! 

Are you absolutely sure that is what you want? Or would you rather let the context of the visual do the segregating for you?

[shirinshon]

Hi ToddChitt

I tried applying it to different sites and it seems it worked (please see below). It's not the best way for me to build the report as I have to create a measure for each site now instead of using a slicer as an example where i would change the site and it would update the visual. 

[ToddChitt]

What's the problem you are trying to solve? What is the ultimate end goal for the visual? Can you supply a mock-up, or describe it? (Screenshots are best, we all like pictures.)

[shirinshon]

So I have 4 tables in my BI report (Earned hours, Targets, Shipments and DimDate). Earned hours report is pulling data from sql server as Shipments table does and they are getting refreshed automatically. Targets table I created in Excel first and then loaded it in BI. It's needed to see targets for the month (shipmets and earned hours targets). Please see the relationship between tables below

This is Targets table and I tried to create a column "Site" but then i would have to repeat dates for example "1/1/2025 3 times because I have 3 sites. And it gives an error as the relationship becomes Many to Many between Targets and EH Daily report tables and Targets and Daily Shipment tables. So I couldnt resolve it. 

This is my visual for Shipments tab. I have a Daily visual (at the top) and MTD (bottom one). This is built only for USA site. My idea was to add a Slicer for "Site" but i still haven't fugire it out.