Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Get data from last year, but not the current month

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?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
TeigeGao
Solution Sage
Solution Sage

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
Not applicable

Månedshåndtering.png

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.