Forum Discussion
Count Values in TTM Measure
So I figured this one out:
to count mnth in current quarter
Count:=CALCULATE(COUNT([Sales]),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-1,QUARTER))
to count mnths in last year quarter:
Last Count:=CALCULATE([Count],SAMEPERIODLASTYEAR('Calendar'[Date]))
then my current quarter total sales would be
SSQUARTER:=if(and([Count]=3,[Last Count]=3),CALCULATE(sum([Sales]),DATESINPERIOD('Calendar'[Date],max('Calendar'[Date]),-1,QUARTER)))
And last year's
SSLastQ:=if(and([Count]=3,[Last Count]=3),CALCULATE([QUARTER],SAMEPERIODLASTYEAR('Calendar'[Date])))
Hi,
Your question is not clear. Share some data, explain the business question and show the expected result.
appreciate the reply
I ll rephrase my question using quarter calculations for compact data representation:
lets assume I have the following data set showing current and last year's first quarter sales of two stores A & B
| Date | Store | Sales |
| Jan-18 | A | 23 |
| Feb-18 | A | 45 |
| Mar-18 | A | 34 |
| Jan-19 | A | 12 |
| Feb-19 | A | 34 |
| Mar-19 | A | 62 |
| Feb-18 | B | 32 |
| Mar-18 | B | 55 |
| Jan-19 | B | 14 |
| Feb-19 | B | 23 |
| Mar-19 | B | 56 |
my aim is to do a same store analysis showing last year quarter vs current year but I only want to show stores that have full 3 month of sales in each quarter. You can see that I have set Store B to be opened in Feb 18 so it does not have Jan-18 sales data thus I want Store B to be excluded from my pivot.
in order to do so, I have created the following measures to calculate Current Quarters sales, Last year Quarter Sales, and Year on Year % change:
Quarter:=CALCULATE(sum([Sales]),DATESINPERIOD('Calendar'[Date],max('Calendar'[Date]),-1,QUARTER))
Last Yr Quarter:=CALCULATE([Quarter],SAMEPERIODLASTYEAR('Calendar'[Date]))
YoY %:=if([Last Yr Quarter]>0,if([Quarter]>0,DIVIDE([Quarter]-[Last Yr Quarter],[Last Yr Quarter])))
my outcomes shows :
| Row Labels | Quarter | Last Yr Quarter | YoY % |
| A | 108 | 102 | 5.9% |
| B | 93 | 87 | 6.9% |
what I cannot figure out is how to identify that Store B does not have full 3 month of sales in last year quarter thus
excluding it from pivot?!!
My initial thought is to find a way to count the number of values excel uses to calculate my current and last year quarter sum and exclude it if less than 3.
hope this explain my problem and I really appreciate your attempt to help.
So I figured this one out:
to count mnth in current quarter
Count:=CALCULATE(COUNT([Sales]),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-1,QUARTER))
to count mnths in last year quarter:
Last Count:=CALCULATE([Count],SAMEPERIODLASTYEAR('Calendar'[Date]))
then my current quarter total sales would be
SSQUARTER:=if(and([Count]=3,[Last Count]=3),CALCULATE(sum([Sales]),DATESINPERIOD('Calendar'[Date],max('Calendar'[Date]),-1,QUARTER)))
And last year's
SSLastQ:=if(and([Count]=3,[Last Count]=3),CALCULATE([QUARTER],SAMEPERIODLASTYEAR('Calendar'[Date])))