Get data from last year, but not the current month

[Anonymous]

I have table of orders with a column "finnished dates".

I sort by this using "Relative date filtering" and "is in the last 1 years". This does however have the unfortunate result that if I look at the current month it is a combination of this month and the previous.

How do I solve this?

By creating a new column which calculates this and sort by this or is there an easier option?

[TeigeGao]

Hi @Anonymous ,

Could you please share the screenshot to us for analysis? I think I can't understand your requirement very well.

Best Regards,

Teige

[Anonymous]

As you can see as it is now the 3:rd of may, and if I as I do now set data to 365 days back or forward, the month of may will comprise of data from this year up til now and data from the last year from this date if we look at historical numbers, and it will comprise of data from this date this year and data up til this date for next year.

I would like to avoid this and have a clean cut in the start of the month or in the end of the month.

For historic data the the cut should be done so it reflects all 12 month done and for futre data it should reflect the next 12 upcomming month.

I hope his clarifies it. 🙂

[Anonymous · Accepted solution]

I found a solution to this my self.

I created a column "Is_Next_12_comming_month"

Is_Next_12_comming_month =
IF(
AND(
'HM Ordre'[StartDato]>='HM Ordre'[Finnish_Date_Next_Month];
'HM Ordre'[StartDato]<'HM Ordre'[Relative_Next_Year]
);
"YES";
"NO"
)

Based of two added columns:

"Finnish_Date_Next_Month"

Finnish_Date_Next_Month = EOMONTH(TODAY();0)+1

and "Relative_Next_Year"

Relative_Next_Year = DATE(YEAR('HM Ordre'[Finnish_Date_Next_Month])+1;MONTH('HM Ordre'[Finnish_Date_Next_Month]);DAY('HM Ordre'[Finnish_Date_Next_Month]))

Which in turn is based on added column "Finnish_Date_Next_Month"

Finnish_Date_Next_Month = EOMONTH(TODAY();0)+1