Forum Discussion
MTD calculation with condition
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?
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" )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] )
6 Replies
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" )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?
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.
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] )
