Help Needed on MTD Aggregate Measure

[mjaitly]

Hi

I need some urgent help on an aggregate measure. I have an MTD report that shows data like 1st to 22nd April, 1st to 22nd March, 1st to 22nd Feb, 1st to 22nd Jan and likewise. It is an MTD like-for-like period monthly comparison report. In my company we upload data daily for the previous day. Please see the attached screenshot. In this aggregated measure, I want to know how instead of manually changing the date every day, I can set the date formula so that it automatically considers yesterday's date.

PS - There is another option in the field and that is TODAY however if I choose this option, figures will not appear correctly as for April (assuming I run the report today 23rd April) it will show data from 1st to 22nd April (as mentioned above we have data till yesterday 22nd april in our servers) but for Mar and preceding months, it will show from 1st to 23rd instead of 1st to 22nd.

Please...any help will be appreciated.

Thanks

Manish

[PeterWijers]

Hi Manish.

i Would suggest a different approach.

create a new aggregated calculated field where you filter on the day number of the month less than the day number of today.

While using the months in the report, it should show the results of each month less than the daynumber of today.

if you might need more help, i could set-up a formula sample for you.

greetings Peter

[mjaitly]

Peter

Thanks for your reply. It will be great if you show me a sample formula.

Thanks

Manish

[PeterWijers]

Manish,

At first it should be a Calculated item and not an aggregated measure.  (Was late yesterday...haha)

First the formula:

So if the "day of the month" of the transaction Date is less than the "day of the month" of today (the 24th "now") then the weight is transfered to the variable kg_form, if not the kg_form gets 0

Here the result.

As you can see in this cross table, all data reporting stops at the 24th day of the month.

This should work for you in this case.

Ones you are used to these kind of calculated items, there are a lot of possibilities.

Greetings and good luck.  Peter

[mjaitly]

Thanks a ton Peter!