DAX puzzle

[XLBob]

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
)
)

[Ashish_Mathur]

Hi,

Share the download link of your file.

[XLBob]

https://onedrive.live.com/?id=7DF5E31C189063EC%21124&cid=7DF5E31C189063EC

It's in the Power BI Files folder

[Ashish_Mathur]

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?

[XLBob]

What I am trying to achieve is that for each quarter I want to check 2 things. First if there is 3 more quarters before the quarter and secondly, the quarter and prior 3 quarters all have sales >0. If this is true, I want to do X otherwise Y. My dates table starts from 1 Jan 2014 but my sales table only has sales starting from Q4 2014.

For 2014Q1, there is no more quarters before it so the check fails.

For 2014Q2, there is only 1 quater before it, the check fails.

For 2014Q3 there is only 2 quarters before it so the check fails again.

For 2014Q4 there is 3 more quarters before it but only 2014Q4 have sales>0, the check fails again.

For 2015Q1 there is 3 more quarters before it but only 2014Q4, 2015Q1 have sales>0, the check fails again.

For 2015Q2 there is 3 more quarters before it but only 2014Q4, 2015Q1,2015Q2 have sales>0, the check fails again.

For 2015Q3 there is 3 more quarters before it and 2014Q4, 2015Q1,2015Q2,2015Q3 all have sales>0, the check succeeds.

For 2015Q4 there is 3 more quarters before it and 2015Q1,2015Q2,2015Q3,2015Q4 all have sales>0, the check succeeds.

So What I want is for row 1 the test measure should return value of 1 because only 1 quarter (201404) has sales (Again, my sales table has data from this quarter onwards), row 2 should have value of 2 because only 2 quarters (201404,201501) have sales, row 3 should have value of 3 because only 3 quarters (201404,201501,201502) have sales, row 4 and on wards should all have value 4.

[Ashish_Mathur · Accepted solution]

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.

[XLBob]

Thanks for your quick reply. I think your DAX code does return the expected result. Do you know why below is returning 1 for all the rows. sometimes understand why something not working is far more important and beneficial than knowing the working formula. Hope you can shed some light. 

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

[Ashish_Mathur]

You are welcome.  I think the portion that is not working is VALUES(Dates[QuarterID]).  A simple approach which i always follow is that if i have a Date column in my dataset, i use Date/Time intelligence functions.  This makes the problem easier to solve.

[XLBob]

If you create a Calculated Table using below DAX code, you will see the expected result. However if I copy the bolded part,replace the EARLIER FUNCTION with VALUES and use it in a measure, it returns 1 for all the rows like you see in the screenshot I attached in my previous reply.

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

[XLBob]

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))