DAX YTD issues, please help

Question

got the following DAX formula trying to calcuate a YTD_SALES : we have year, month, plant name, territory, subregion as filter, market segment, customer, item no as Row labels

=CALCULATE(sum(MarketSegments[Actual USD]),DATESYTD(MarketSegments[MStartDate]),ALLEXCEPT(MarketSegments,MarketSegments[YEAR],MarketSegments[MONTH],MarketSegments[PLANT NAME],MarketSegments[TERRITORY],MarketSegments[SUB REGION],MarketSegments[MARKET SEGMENT],MarketSegments[CUSTOMER],MarketSegments[ITEM NO]))

IT WORKED GOOD IN MARKET SEGMENT LEVEL, BUT SOME HOW, WHEN WE TRYING TO GO CUSTOMER AND ITEM NO LEVEL, THE NUMBERS ARE NOT CORRECT, EXAMPLE, ONE CUSTOMER A DOES NOT HAVE INVOICE ON FEB 2012, BUT THEY HAVE SALES ON 201201, SOMEHOW, THIS FORMULAR STOP THAT CUSTOMER SHOWED FOR YTD FOR FEB2012, IS THAT RELATED TO THE VALUE TO BE NULL IN FEB? BUT FOR JULY, IT DOES NOT HAVE SALES VALUE EITHER, BUT SOMEHOW, YTD STILL SHOWED.....

ANY IDEA?

THANKS IN ADVANCE

Answer 1

got the following DAX formula trying to calcuate a YTD_SALES : we have year, month, plant name, territory, subregion as filter, market segment, customer, item no as Row labels

=CALCULATE(sum(MarketSegments[Actual USD]),DATESYTD(MarketSegments[MStartDate]),ALLEXCEPT(MarketSegments,MarketSegments[YEAR],MarketSegments[MONTH],MarketSegments[PLANT NAME],MarketSegments[TERRITORY],MarketSegments[SUB REGION],MarketSegments[MARKET SEGMENT],MarketSegments[CUSTOMER],MarketSegments[ITEM NO]))

this formula works perfect on any level above market segment, but somehow, after that , it is not working as expected, the problem is there are some data missing in the customer and item no level, when you try to sum the customer level to market segment level, the total not matching with market segment total....

NOTE: we do not have a real time dimention in the cube, only a year month table, so that is why we can use Monthstartdate here, cause it will aggregate to month level, i tried to create YTD_Sales as calculated member in cube, it works perfectly with my user defined hierarchy in dimentin month, but there is a user defined hierarchy which i do not think power pivot can handle, can some one give some suggection? Thanks in advance