Using the TOTALMTD function but only on business days
Hi guys, I am trying to create a measure that allows me to accumulate the sales for the month, so I am using the TOTALMTD function, this works correctly on the filtered month (September). I also want to get the previous month (August), so I add the filter DATEADD(CALENDARIO[Date],-1,month) , however the value is repeated when it is NOT a weekday in August but it is a weekday in September . I have a calendar table with business days and related to the sales table through dates. Example: expectation: My measures: September = SUM(FCT_VENTAS[MONTO_K]), August = CALCULATE( SUM(FCT_VENTAS[MONTO_K]), DATEADD(CALENDARIO[Date],-1,month) ) TOTALMTD (sep) = TOTALMTD( SUM(FCT_VENTAS[MONTO_K]), CALENDARIO[Date] ) TOTALMTD (ago) = TOTALMTD( SUM(FCT_VENTAS[MONTO_K]), DATEADD(CALENDARIO[Date],-1,month) ) thank a lot!
TOTALMTD and USERELATIONSHIP
Hi All, I am trying to calculate a MTD value based on a date column from my 'Quotes' table that is different to the date column that has an active relationship with my calendar. I think I need to use some sort of combination of TOTALMTD and USERELATIONSHIP? I am calculating a value from my 'Quotes' table. The 'Quotes' table has a 'Created Date' column, which is my active relationship with my calendar table, and a 'Quote Won Date', which is what I want my month-to-date calculation to work from. I do have an inactive relationship established in my model between my calendar table and the 'Quote Won Date' Here is something I have tried which does not work but might illustrate what I am trying to calculate: MTD Value of Quotes Won by Quote Won Date = CALCULATE([Value of Quotes Won],DATESMTD('calendar'[Reporting Date]),USERELATIONSHIP(quoted_opportunities_and_quotes_data[Quote Won Date],'calendar'[Reporting Date])) Really appreciate any help anyone can offer! Many thanks in advance, Chris
