cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ivamsikrishna
Helper III
Helper III

Trailing 12 months Counts in chart

Hi 

We have a data table with two data columns (Order Date, Order Number, OrderMonthYear ) and I am trying to display rolling 12 months and previous rolling 12 months counts in a line and stacked column chart. Using rolling filter and sameperiodlastyear function I was able populate current and previous rolling details.  But I have another chart wheer I supposed to display order count for previous rolling 12 months only skipping current rolling 12 months. For example todays date is June/11/2021 so I am trying to display in chart count starting from June/01/2019 to May/31/2020. I try to adding calculation like below but no luck. Please help how to achieve this. 

Rolling12MonthPrevtoPrev = var startdate=MONTH(today())-23
var enddate=MONTH(TODAY())-13
return
CALCULATE(DISTINCTCOUNT(Sheet1[Order ID]),FILTER(Sheet1,MONTH(Sheet1[Order Date].[Date])<=startdate))
 
Thanks,
Krishna.
 
 
1 ACCEPTED SOLUTION

@ivamsikrishna 

pls see the attachment below, is this what you want?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
ryan_mayu
Super User II
Super User II

@ivamsikrishna 

is this what you want?

Measure = 
VAR _start=EOMONTH(EDATE(today(),-25),0)+1
VAR _end=EOMONTH(EDATE(today(),-13),0)
return CALCULATE(DISTINCTCOUNT('Table'[Order ID]),FILTER('Table','Table'[Order Date]>=_start&&'Table'[Order Date]<=_end))

please see the attachment below





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It is working charm, thanks for the help. Quick thing when I drag measure to values, in the chart I am getting skipped months. Is there any field settings I should change. Currently X axis is in Continuouse. When I change it to Categorical the months displaying correct but I am expecting to display as month-Year

 

ivamsikrishna_0-1623674869333.png

 

 

 

@ivamsikrishna 

pls see the attachment below, is this what you want?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Thanks for the attachment, yeah it is how I am expecting. Appreciate.

ivamsikrishna
Helper III
Helper III

Below please find the sample data

Order ID Order Date
CA-2017-126221 12/30/2021
CA-2017-143259 12/30/2021
CA-2017-119305 11/30/2021
CA-2017-116645 11/30/2021
CA-2017-104136 11/1/2021
CA-2017-104136 11/1/2021
CA-2017-131632 10/31/2021
CA-2017-117114 10/31/2021
CA-2017-117114 10/31/2021
CA-2017-128041 9/1/2021
CA-2017-128041 9/1/2021
CA-2017-159884 8/31/2021
CA-2017-159884 8/31/2021
US-2017-148362 7/1/2021
US-2017-148362 7/1/2021
US-2017-148362 7/1/2021
CA-2017-102946 6/30/2021
US-2017-102890 6/30/2021
CA-2017-131695 6/30/2021
CA-2017-110198 5/1/2021
CA-2017-110198 5/1/2021
CA-2017-151911 4/30/2021
CA-2017-144526 4/30/2021
CA-2017-144526 4/30/2021
CA-2017-155712 3/2/2021
CA-2017-124114 3/2/2021
CA-2017-101959 2/28/2021
CA-2017-101959 2/28/2021
CA-2017-117212 2/26/2021
CA-2017-151750 1/1/2021
CA-2017-151750 1/1/2021
CA-2017-160395 12/31/2020
CA-2017-160395 12/31/2020
CA-2017-100202 12/31/2020
CA-2016-100671 11/1/2020
CA-2016-129196 11/1/2020
CA-2016-119165 10/31/2020
CA-2016-144218 10/31/2020
US-2016-142251 10/31/2020
CA-2016-140130 10/31/2020
CA-2016-116526 9/1/2020
CA-2016-116526 9/1/2020
CA-2016-124583 9/1/2020
CA-2016-117408 8/31/2020
US-2016-141544 8/30/2020
US-2016-141544 8/30/2020
US-2016-126431 7/1/2020
US-2016-158680 7/1/2020
CA-2016-162355 6/30/2020
CA-2016-162355 6/30/2020
CA-2016-135594 6/30/2020
CA-2016-101525 5/1/2020
CA-2016-154788 4/30/2020
US-2016-120460 4/30/2020
US-2016-151862 3/1/2020
CA-2016-111283 2/29/2020
CA-2016-101623 2/28/2020
CA-2016-102456 2/27/2020
CA-2016-101448 2/27/2020
CA-2016-128706 2/27/2020
CA-2016-153661 1/30/2020
US-2016-166660 1/30/2020
US-2016-166660 1/30/2020
CA-2016-114944 1/30/2020
CA-2016-168032 1/30/2020
CA-2015-110814 12/5/2019
CA-2015-110814 12/5/2019
CA-2015-149566 12/5/2019
CA-2015-149566 12/5/2019
CA-2015-141740 11/1/2019
CA-2015-131457 10/31/2019
CA-2015-122259 10/31/2019
CA-2015-162537 10/28/2019
CA-2015-162537 10/28/2019
CA-2015-162537 10/28/2019
CA-2015-139290 10/26/2019
CA-2015-156146 10/26/2019
CA-2015-156146 10/26/2019
CA-2015-156566 10/1/2019
CA-2015-169201 9/28/2019
CA-2015-169201 9/28/2019
CA-2015-104626 9/1/2019
CA-2015-104626 9/1/2019
CA-2015-112319 8/31/2019
CA-2015-111514 8/31/2019
CA-2015-111514 8/31/2019
CA-2015-153388 8/1/2019
CA-2015-153388 8/1/2019
CA-2015-124891 7/31/2019
CA-2015-124891 7/31/2019
CA-2015-124891 7/31/2019
CA-2015-128860 6/29/2019
US-2015-141684 6/29/2019
CA-2015-165799 6/29/2019
US-2015-100069 6/29/2019
CA-2015-124919 5/31/2019
CA-2015-124919 5/31/2019
CA-2015-124919 5/31/2019
CA-2015-100818 5/31/2019
CA-2015-100818 5/31/2019
CA-2015-104486 5/1/2019
CA-2015-104486 5/1/2019
US-2015-164175 4/30/2019
CA-2015-135174 4/30/2019
CA-2015-103716 4/30/2019
CA-2015-103716 4/30/2019
CA-2015-128125 3/31/2019
CA-2015-133494 3/1/2019
CA-2015-133585 3/1/2019
CA-2015-133585 3/1/2019
CA-2015-104052 3/1/2019

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors