how to get the last month data in the output table (set analysis as dimension)

[Sara_3]

Hello,

I need to do 2 filters applied in the output data table  :

1- Code = B4*

2- Last month data like my max "Release Date" is July i need the last month data (June 2024)

I already get the Code = B4* data in the table as dimension  :

=aggr(Only({< [ Code]={"B4*"}>}[Code]),[Code])

I am having a problem to get last month data in the table. my date field is "Release Date" and format is "MM/DD/YYYY". 

Requirement : Last month data in the table as dimension with date format is "MMM/YYYY"

I have tried to create a variable of last month data like :

Variable:

date(max([Release Date]-1), 'MMM/YYYY') ---- in kpi I got the correct value like Jun 2024

but how do I include this variable into my above aggr () set analysis to only get the last month data dynamically? 

Thanks in advance,

Sara.

[chriscammers]

First of all your use of AGGR to make you "Code" dimension is not needed. All you have to do is use the "Code" field as a dimension.

Filtering only happens on measures in set analysis so in order to keep the other values of Code out of the results you have to uncheck the "show zero values" in the data add ons/data handling section of the chart properties.

This is an instance where a "calendar dimension" table is very handy but you can calculate what you need in set analysis. So let's take a look.

• The Current month data - the expression does a search for dates between the month start and month end of the greatest avaialble month.

Sum({<Code = {'B4'},ImportantDate = {"$(='>=' & MonthStart(Max(ImportantDate)) & '<=' & MonthEnd(Max(ImportantDate)))"}>}MeasureField)​

• The Previous Month Data - subtract one month from the calculated search range

Sum({<Code = {'B4'},ImportantDate = {"$(='>=' & MonthStart(Max(ImportantDate),-1) & '<=' & MonthEnd(Max(ImportantDate),-1))"}>}MeasureField)

In the second expression we use the second parameter of the Monthstart and MonthEnd Functions to offset the range of dates to 1 month prior.

There are easier ways to do this with calendar tables but if you only have the date field this will work for you.

[Sara_3]

Thank you but I don't have any Measure to SUM or count . I need the data in my output table is Code starts with "B4*" and last month data of "Release Date" field .

that is why I am using Only in my set analysis with aggr(). 

I have tried this but getting NULL value;

Only({<[Code] = {'B4*'}, 'Release Date" = {"$(='>=' & MonthStart(Max("Release Date"),-1) & '<=' & MonthEnd(Max("Release Date"),-1))"}>}"Release Date").

Please correct me if I am doing something wrong. 

I appreciate it!

[marksouzacosta]

Hi @Sara_3,

Are you trying to do something like this?

MonthName(MonthStart(Max({<O_ORDERPRIORITY = {'1-URGENT'}, O_ORDERDATE = {">=$(=MonthStart(AddMonths(Max({1} O_ORDERDATE),-1)))<=$(=MonthEnd(AddMonths(Max({1} O_ORDERDATE),-1)))"}>} O_ORDERDATE)))

[chriscammers]

Ok, you have to aggregate something... how about the release date??

Since I don't know the other items in the table you are trying to show it is hard to tell you what to do I have seen a few ways of doing this like

//The "important date"
Min({<Code = {'B4'},ImportantDate = {"$(='>=' & MonthStart(Max(ImportantDate),-1) & '<=' & MonthEnd(Max(ImportantDate),-1))"}>}ImportantDate)

//the ID field of the items you want to list
Count({<Code = {'B4'},ImportantDate = {"$(='>=' & MonthStart(Max(ImportantDate),-1) & '<=' & MonthEnd(Max(ImportantDate),-1))"}>}IDField)

//Get really slick to show the actual ID as a measure
//Concat creates a delimeted list and then subfield gets the 
//first value
Subfield(Concat({<Code = {'B4'},ImportantDate = {"$(='>=' & MonthStart(Max(ImportantDate),-1) & '<=' & MonthEnd(Max(ImportantDate),-1))"}>}Distinct IDField,'||'),'||',1)