Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.
 
 
Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    5 years ago

    Anonymous 

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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