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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Jan845
Helper I
Helper I

Relative Date last month not current month


Hello everyone

Is it possible in Powerbi in the Relative date filter, in the Last Months option, to select not the current month but from the last day of the previous month?

Currently I select for example last 12 months, it shows the current month (March), but the month is not over yet, I wanted it to start from the last day of last month.

Is this possible in PBI?

Can anyone help solve this in my file?

 


Translated with DeepL.com (free version)

 

Jan845_0-1710350433004.png

 

My PBIX file: https://stealth.cyborg.co/r#0sft8VMnmcdEEcfopkLER2XiQIt5ZbICKVUoLV68UlU

 

Thanks for help guys.

1 ACCEPTED SOLUTION
v-huijiey-msft
Community Support
Community Support

Hi @Jan845 ,

 

The Relative Date filter automatically adjusts to the current date and does not natively support starting from the last day of the previous month directly in the filter options.

 

Alternative is:

 

Create a calculated column that represents dates within the past 12 months starting on the last day of the previous month.

Column1 =
IF(
     Table1[Data] > EDATE(EOMONTH(TODAY(), -1), -11)
     &&
     Table1[Data] <= EOMONTH(TODAY(), -1),
1, 0)

 

Instead of using the built-in "Relative Date" filter, use the created calculated column as a filter in the report. Filter to include only rows equal to 1.

vhuijieymsft_0-1710382227232.png

 

For more information about the EOMONTH function, please see:

EOMONTH function (DAX) - DAX | Microsoft Learn

 

If you have any further questions please feel free to contact me.

 

pbix file is attached.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

1 REPLY 1
v-huijiey-msft
Community Support
Community Support

Hi @Jan845 ,

 

The Relative Date filter automatically adjusts to the current date and does not natively support starting from the last day of the previous month directly in the filter options.

 

Alternative is:

 

Create a calculated column that represents dates within the past 12 months starting on the last day of the previous month.

Column1 =
IF(
     Table1[Data] > EDATE(EOMONTH(TODAY(), -1), -11)
     &&
     Table1[Data] <= EOMONTH(TODAY(), -1),
1, 0)

 

Instead of using the built-in "Relative Date" filter, use the created calculated column as a filter in the report. Filter to include only rows equal to 1.

vhuijieymsft_0-1710382227232.png

 

For more information about the EOMONTH function, please see:

EOMONTH function (DAX) - DAX | Microsoft Learn

 

If you have any further questions please feel free to contact me.

 

pbix file is attached.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.