Max Month Year -1

[Daniel77]

Hi All,

Dates have ruined my afternoon and I need some help please.

I have an application loading data with a date field as follows:

Date(date#(POD,'MM/DD/YYYY hh:mm:ss TT'),'DD/MM/YYYY') as Date,

I need to run the report on a schedule on the 1st of the month showing all data from the previous month using the above  Date field. Can anyone offer some assistance please?

Thanks so much in advance.

Daniel

[marksouzacosta]

Hi @Daniel77,

We need more details of what are you trying to achieve. What do you mean by "run the report"? What is the content of your charts and what are your measures expressions?

Meanwhile, I would suggest a small adjustment in your date field - the field you have now is actually a TimeStamp formatted as a Date field. Try this:

Date(Floor(date#(POD,'MM/DD/YYYY hh:mm:ss TT')),'DD/MM/YYYY') as Date,

Regards,

Mark Costa

[Daniel77]

Hi,

Thank you for the reply, by run i mean, on a schedule the report will run on the 1st of the month. The application will import data which daily and the report will grow. in the monthly report, i need to only include data for the previous month only (if i am running on the 1st Sept, it should include data from August).

The report is a straight table with no expressions, only dimensions including the Date field.

Thanks

Daniel

[Daniel77]

I have tried something like:

Only( {< YearMonth = {"$(=max(YearMonth -1))"}>} [Portal Order No.])

as a measure but am getting no return while the expression look ok and shows:

Thanks again for the help on this.

Daniel

[poklegoguy]

Not sure if you want to achieve this in script / data model level or chart level. If for chart, there's a lot of way to configure dates. You can use set analysis as below to get the first date of last month to date of today in measure expression:
{$<Date = {">=$(=MonthStart(Today(),-1))<=$(=Today())"}>}

With the set analysis above, if today's date is 10/9/2024, you will get the data from 1/8/2024 - 10/9/2024.

If you insisted on using Month, you can use the expression below:
{$<YearMonth  = {"=$(=Date(AddMonths(Max(Date), -1)), 'YYYY-MM')"}>}

Replace the date format with the format you used for YearMonth field.

[Kushal_Chawda]

@Daniel77  you need to use proper aggregation functions like sum, count depending on the requirements. Assuming here you want to count the order number so try below

count( {< YearMonth = {"$(=max(YearMonth) -1)"}>} [Portal Order No.])

[Daniel77]

Hi,

Thank you for the reply, 

I have implemented in a small table and while the expression shows the expected dates:

(running the report on the 1st September should return 1 whole month)

When adding the portal order number dimension, i still see all Dates.

Do you know why this is happening please? I was hoping to use a dimension so that nulls were created for the unrequired dates and i could tick to not show the nulls.

Thank you for the assistance.

Daniel

[Kushal_Chawda]

@Daniel77  Well, I can suggest the solution if you can tell me what exactly you want in your report?

[poklegoguy]

Can you specify your chart requirements? Like what should be the dimension and measure in the table? If Date is your dimension and you wanted to find the count of order number, use date as the dimension and  the expression below in your measure expression:

COUNT({$<Date = {">=$(=MonthStart(Today(), -1))<=$(=Today())"}>} DISTINCT [Order No])

This will filter the date. If you want to use YearMonth though, use something as below:

COUNT({$<YearMonth = {"=$(=Date(MAX(YearMonth)-1, 'YYYY-MM')"}>} DISTINCT [Order No])

[Daniel77]

Hi All,

Apologies for not being more specific, I didnt share a screen shot or all dimensions as there are over 100 dimensions in the report collecting all order information fields.

Key fields are:

Portal Order No.
Date

I would like to show ALL portal Order No for the Previous Month year Only (when running the report on the 1st of the month). On Sept 1st, I would like to see all Portal Order No. with a date in August 24.

There are currently no aggregations in the report like SUM/Count.. This is why i tried Only() to display the required using set analysis.

Thanks again for the time you have taken in your replies so far! 

Daniel