Forum Discussion

XLBob's avatar
XLBob
Icon for Resolver II rankResolver II
9 years ago
Solved

DAX puzzle

I am trying to write a DAX measure which counts number of rows where current and prior 3 quarters have sales>0. My model is very simple, it has dates table and fact table. The dates table looks like below image.

What I want is for row 1 the test measure should return value of 1, row 2 should have value of 2, row 3 should have value of 3, row 4 and on wards should all have value 4.


Below is the DAX code for my test measure I have right now but it's not giving me what I want. I don't know which part of it is not right.
Test =
VAR CurrentQuarter =
SELECTEDVALUE ( Dates[QuarterID] )
RETURN
COUNTROWS (
FILTER (
ADDCOLUMNS (
FILTER (
ALL ( Dates[QuarterID] ),
Dates[QuarterID] <= CurrentQuarter
&& Dates[QuarterID]
> CurrentQuarter - 100
),
"SaleZ", [Total Sales]
),
[SaleZ] > 0
)
)

 

 

 

 

 

 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    9 years ago

    Hi,

     

    Try this calculated field formula

     

    =if(ISBLANK([Total Sales]),BLANK(),COUNTROWS(FILTER(CALCULATETABLE(SUMMARIZE(Dates,Dates[Quarter],"ABCD",[Total Sales]),DATESBETWEEN(Dates[Date],EDATE(MIN(Dates[Date]),-9),MAX(Dates[Date]))),[ABCD]>0)))

    Look at measure2 in the second table.  Here's the file.

     

9 Replies

  • XLBob's avatar
    XLBob
    Icon for Resolver II rankResolver II
    9 years ago

    If I use Calculated Table, I can see that my DAX expression does work as expected. My sales table only has sales from Q4 2014 (QuarterID 201404) so row count returns 1.

    If I change my VAR to 201604, you can see that it returns the correct value of 4.But when I use the expression in a meaure (see screenshot in my first post of this thread), it returns 1 for all rows. Anyone can help explain why it's doing this.

    COUNTROWS(FILTER(
    ADDCOLUMNS(
    FILTER(ALL(Dates[QuarterID]),Dates[QuarterID]<=SELECTEDVALUE(Dates[QuarterID])&&Dates[QuarterID]>SELECTEDVALUE(Dates[QuarterID])-100)
    ,"SaleZ",[Total Sales]
    )
    ,[SaleZ]>0))

     

     

     

     

     

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        9 years ago

        Hi,

         

        I am not sure of what result you are expecting.  I think you want to compute the Total sales for the 4 quarters ending that row's quarter.  So if you look at 2015 Q4, then you want to sum up the sales of 2015Q1+2015Q2+2015Q3+2015Q4.  Atleast that is what i think you want.  Please see the second table int he screenshot below.

         

         

        Is this your expected result?