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