Get data for latest reporting month

[Former Member]

Hi team,

I have data in one of our column period as 201601,201602,201603 etc

now user may select multiple values but would want to display data for latest month only

say 201607 is latest so if i have a measure Revenue then revenue should be calculated only for 201607

I tried using below formula but it failed on one condition and i seriously have no clue right now to solve it

=[revenue] Where ([Reporting Period] =

FormatNumber(Max(ToNumber([Reporting Period]));"0") ForAll ([Reporting Period]))

this is failing in one instance where say i have a value in 201606 ie) previous month

but though i tell the formula to take the latest value which is 201607

in my result i am getting values for previous month value also

Any ideas on how can we solve it

I can't use currentdate logic because i can run my reports across multiple reporting periods so currentdata logic will fail

Regards,

Akshat

[amitrathi239 · Accepted solution]

try this.

=[revenue] Where ([Reporting Period] )=Last([Reporting Period]) In Report)

[Former Member · Accepted solution]

Dude that was great

i actually created majority of my formulas using max

and never gave problems until now

thanks a lott, i can i guess redevelop my formulas

[amitrathi239 · Accepted solution]

Not in every case you can use Last() function.If you have to find max value in the column and which is not in the end that time you have to use MAX.

in your case you will get period month in last so you can use LAST here.

[Former Member · Accepted solution]

Yaa but is my syntax for calculating correct?

=[revenue] Where ([Reporting Period] =

Max([Reporting Period]) ForAll ([Reporting Period]))

i had to use forall in order to get it for last month

[amitrathi239 · Accepted solution]

Yes it's correct.

Check if you are getting the MAX reporting period with this formula.

=Max([Reporting Period]) ForAll ([Reporting Period]

Make sure Reporting Period object should be number datatype.if it is string then you will not the correct result.

[Former Member · Accepted solution]

Ya i am getting

its just the entire logic is failing

if we have a value for previous month but not for current

eg:

data in 201604

abc  10

def   5

data in 201605

abc 5

def   ""

so when i run the report in 201605 using max and forall i am getting data as

abc 5

def  5 (which it's showing for 201604 may be because of forall)???

[Former Member · Accepted solution]

Hi Amit,

a quick help
I was trying to use last logic on one of my formulas

formula is : To calculate/show Revenue for total year, say its 2016 so 2015 should be there

=[Revenue] Where (Left([Reporting Period];4) =FormatNumber(ToNumber(Left(Last([Reporting Period]);4))-1;"0"))

But it's displaying as blank though we have values

any idea what might be the issue?

[amitrathi239 · Accepted solution]

what is the value you are getting with this formula.

=FormatNumber(ToNumber(Left(Last([Reporting Period]);4))-1;"0")

or try this.

=(FormatNumber(ToNumber(Left(Last([Reporting Period]);4))-1;"0") In Report)

[Former Member · Accepted solution]

Oh God!!! so foolish of me....

i absolutely forgot to write in report clause!

thanks a lot and sorry dude for troubling over such petty request