13 Replies Latest reply: Aug 21, 2017 7:47 AM by Camille Menten

# Graph with data Dec over different years + MTD for current year

Hi all,

I'm quite stuck in my set analysis for the following:

I want to count the nrs of account we have in the month december of eacht year since 2014. For the current year, I want to have the nrs for the last month.

I have tried different set-ups but none work.

I have identified the max month per year throught following formula:

Max({<IsActual_={1}>} Month)

Combined with year it gives me the below as expected

However, when I try combining this into the count of my accounts, it doesn't work.

count( {<[Month] = {"\$(=Max({<IsActual_={1}>}[Month]))"}> }distinct [DMU])

I guess because Max({<IsActual_={1}>}[Month] is giving a numeric result (12 or 7) while my field Month is giving 'Dec' or 'Aug' as result.

Then I tried

if (

Year = Year(Today()),

count( {< Month = {'Month(Today())'} > } distinct [DMU]),

Count( {< Month = {'Dec'} >} distinct [DMU])

)

this gives me all values but for 2017

help is welcome ...

• ###### Re: Graph with data Dec over different years + MTD for current year

Try this

RangeSum(

Count({<Month = {'\$(=Month(Today()))'}, Year = {\$(=Year(Today())}>} DISTINCT [DMU]),

Count({<Month = {'Dec'}, Year -= {\$(=Year(Today())}>} DISTINCT [DMU])

)

• ###### Re: Graph with data Dec over different years + MTD for current year

hello,

doesn't seem to work

• ###### Re: Graph with data Dec over different years + MTD for current year

Would you be able to share a sample?

• ###### Re: Graph with data Dec over different years + MTD for current year

no sorry, cannot do that.

• ###### Re: Graph with data Dec over different years + MTD for current year

Seems like I missed two parenthesis, try this

RangeSum(

Count({<Month = {'\$(=Month(Today()))'}, Year = {\$(=Year(Today()))}>} DISTINCT [DMU]),

Count({<Month = {'Dec'}, Year -= {\$(=Year(Today()))}>} DISTINCT [DMU])

)

• ###### Re: Graph with data Dec over different years + MTD for current year

but still doesn't give me the count.

why do you do a range sum?

• ###### Re: Graph with data Dec over different years + MTD for current year

correction. it does work - except for 2017

• ###### Re: Graph with data Dec over different years + MTD for current year

Do you have data for the month of August in 2017?

• ###### Re: Graph with data Dec over different years + MTD for current year

partially.

but complete data will be for july

• ###### Re: Graph with data Dec over different years + MTD for current year

So do you want July's data or Aug's data?

• ###### Re: Graph with data Dec over different years + MTD for current year

you're right, confusing. sorry. It'll be better to have last month's data. so july in our case now (but agust for next month)

• ###### Re: Graph with data Dec over different years + MTD for current year

RangeSum(

Count({<Month = {'\$(=Month(AddMonths(Today(), -1)))'}, Year = {\$(=Year(Today()))}>} DISTINCT [DMU]),

Count({<Month = {'Dec'}, Year -= {\$(=Year(Today()))}>} DISTINCT [DMU])

)

• ###### Re: Graph with data Dec over different years + MTD for current year

yes!! it works perfectly! Thanks a lot