Forum Discussion

AC23VM's avatar
AC23VM
Helper II
1 year ago
Solved

Measure for combining multiple months into one?

Hi there.  I've made a clustered column chart that shows the top 5 from a set of values over the past 3 months.  With a date column forming my X axis, I end up with 3 sets of 5 columns - the top 5 in Oct, the top 5 in Nov, and the top 5 in Dec.  Is there a quick win to combine these together, so the chart shows just 5 columns, as opposed to 15?  I'm not sure if something like a date measure for 'last 3 months' exists, and if it would work for an X axis - hopefully that makes sense.

Need Help
  • Uzi2019's avatar
    Uzi2019
    1 year ago

    Hi AC23VM 

     

    you can try these formula

    Column =
VAR CurrentDate =
    DATE ( 2017, 12, 31 )
VAR StartingPoint =
    EOMONTH ( CurrentDate, -3 ) + 1
RETURN
    Table1[Date]
    >= StartingPoint
    && Table1[Date] <= CurrentDate

    OR

     

    CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH)

     

    I hope I answered your question!

     

2 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi AC23VM 

     

    It would easier for us to provide a working or near-working solution if you posted a workable sample data (not an image), your expected result from the same sample data and the reasoning behind. Also, what column are you ranking? Top 5 what based on what value?

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    1 year ago

    Hi AC23VM 

     

    you can try these formula

    Column =
VAR CurrentDate =
    DATE ( 2017, 12, 31 )
VAR StartingPoint =
    EOMONTH ( CurrentDate, -3 ) + 1
RETURN
    Table1[Date]
    >= StartingPoint
    && Table1[Date] <= CurrentDate

    OR

     

    CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH)

     

    I hope I answered your question!