- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

I have a powerpivot table that lists sales data for stores. The main driver is the YearMonth which is a varchar(6) field. This is also a slicer along with state, StoreType (Dealer or Corporation), StoreCycle, StoreWarehouse (where store delivers from). While selecting a YearMonth on the slicer (ie. 201212), the StoreCycle (ie. 11) and the StoreType (ie. Dealer) I count the CurrYrNbrStores (Current Year number of stores open) using the following measure: =sum('StoreData'[StoreOpen]) where StoreOpen is an integer with value of 1 if the store was open or 0 if it was closed. The calculation comes out correct for all of the slicers I select. My problem is when trying to calculate the PrevYrNbrStores (previous year number of stores) while sitting on the current year YearMonth slicer. From my example I need to go to 201112 and count how many 1 I have on the StoreOpen column considering the slicers currently selected. To accomplish this I use the countrows Dax measure as follows: =COUNTROWS(FILTER(StoreData, (DISTINCT(StoreData[YearMonth]) = DISTINCT(StoreData[YM12Back]) && StoreData[StoreOpen]=1))) ..where YM12Back is a column that has the same month for the previous year, in my example 201112. The result comes back blank. If I use >= on this measure (just to see if I get a number) it would give me the CurrYrNbrStores. I have the StoreData table linked to other tables; Cycle by CycleNbr, Store by StoreNbr and YearMonth by the YrMonth. I wonder if this links are preventing me from getting another set of data (filtering) for a previous year using the countrows Dax function. I've also used SUMX, COUNTX, COUNTAX functions with no luck. Thanks in advance for your help.

power-pivot
Comment

**11** People are following this question.

Copyright 2022 Redgate Software.
Privacy Policy