cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ARTarabein
New Member

Count Values in TTM Measure

Hi, 

 

I am trying to come up with the count of values that are yielding my TTM Measure total. The aim is to make sure that there is always 12 month of values yielding my current and Last Year TTM otherwise I want to exclude from dashboard (it is a same store dashboard) 

 

I am using the following measure:

TTM:=CALCULATE([Mnth],DATESINPERIOD(dDate[Date],max(dDate[Date]),-12,MONTH))

Last Yr TTM:=CALCULATE([TTM],SAMEPERIODLASTYEAR(dDate[Date]))

YoY TTM %:=if([Last Yr TTM]>0,if([TTM]>0,DIVIDE([TTM]-[Last Yr TTM],[Last Yr TTM])))

 

Thank you in advance

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User III
Super User III

Hi,

Your question is not clear.  Share some data, explain the business question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

DateStoreSales
Jan-18A23
Feb-18A45
Mar-18A34
Jan-19A12
Feb-19A34
Mar-19A62
Feb-18B32
Mar-18B55
Jan-19B14
Feb-19B23
Mar-19B56

 

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 LabelsQuarterLast Yr QuarterYoY %
A1081025.9%
B93876.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])))

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors