MicroStrategy Prompt Answer Get Month

I am learning MicroStrategy and I am doing an exercise that I do not know how to do it.

I have in a report several attributes, one of them is a date, and one metric. With this metric, I did derived metrics using filters to get diary, monthly and annually data from the first metric (based on the attribute date). These metrics have a prompt answer for a date. However, with the monthly metric I have some problems. This is the explication:

• If I execute the report on 02/12/2023, the metric monthly has to display the previous month, i.e., from 01/11/2023 to 30/11/2023, with its data, due to the month is not completed.
• If I execute the report on 05/11/2023, the metric monthly has to display all the data of this month because the month is completed.

Any ideas how to get this information? I can do metrics, filters... but I do not know how to that.

Thanks a lot!

To do the accumulated production depending on whether the month is complete or not (it is considered complete when every day has passed in the month), the following steps must be followed:

I can explain better with an example. Let's imagine that we execute the report on 03/12/2023. So:

Create 2 filters in the attribute date

• The first one has to collect the dates of the month in which report is executed. For that reason, the filter gets the dates between 01/12/2023 and 03/12/2023. To do this filter, you can use the following functions: AddMonths and MonthStartDate (with the prompt answer)

• The second one has to collect the dates of the previous month in which report is executed. Therefore, the filter gets the date between 01/11/2023 and 30/11/2023. To do this filter, you can use the following functions: AddMonths, MonthStartdate and MonthEndDate.

Create 2 metrics

The next step is to create the metrics associated with each of the filters.

In addition, in the report or dossier, we have to create two metrics. The first is to get the maximum dates that the report has. The next is to do the conditional metric. You have to use de functions: Sum, If, Month and CurrendDate. So, the formula is like this:

sum(if(month(currentdate())=month("attributedate"), a, b))

where a is the first metric with the first filter; b the second one.

pd: The formula represents the idea of how to put it in MicroStrategy. You must adjust the formula with your conditions.