Relative Dates For Automated Reporting

[SparkDataGuru]

I have a report which has data uploaded daily up to month to date prior (for example: if today is Dec 20, 2022, I have data from Dec 1 to 19, 2022). Currently we are utilizing 'Filters on all pages' to filter the 'Month Year' to a 'relative date is this month'. This works fine for all days until Jan 1, 2023...

How can I ensure that the report will show Dec 1 to 31, 2022 when the calendar date is Jan 1, 2023? 

Thank you for your help!

[Anonymous · Accepted solution]

Hi  @SparkDataGuru ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _today=TODAY()
return
SWITCH(
    TRUE(),
    _today=DATE(YEAR(_today),MONTH(_today),1)&&
    MAX('Table'[Date]) >=DATE(YEAR(_today),MONTH(_today)-1,DAY(_today))&&
    MAX('Table'[Date]) <_today,1,
     _today<>DATE(YEAR(_today),MONTH(_today),1)&&
    MAX('Table'[Date]) >=DATE(YEAR(_today),MONTH(_today),1)&&MAX('Table'[Date])<_today,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[themistoklis]

@SparkDataGuru 

I believe you will find the answer in the following topic.

https://community.powerbi.com/t5/Desktop/Relative-Date-Filtering-Based-On-Yesterday/m-p/728896