Filter data for a specific day of the month

[CarlosOlmos29]

Hello Power BI community.

Previously I had some data that was loaded with the last day of each month. I had the following measure:

Graph_dispersion_mes = CALCULATE(DIVIDE(sum(Table[Real Oil])-sum(Table[Plan Oil]),sum(Table[Plan Oil])),Table[Date]=EOMONTH(TODAY(),-1)).
Now the data is loaded with the 15th day of each month. That is, the January data is for 15-01-2023, the February data for 15-02-2023 and so on. I have used two different measures but none of them worked for me:

1. Graph_dispersion_mes = CALCULATE(DIVIDE(SUM(Actuals[OIL])-SUM(Plan[OIL]),SUM(Plan[OIL])), Date[Date]=DATE(2023,04,15)).
Graph_dispersion_mes = CALCULATE(DIVIDE(SUM(Actuals[OIL])-SUM(Plan[OIL]),SUM(Plan[OIL])), Date[Date]=max(actuals[date])).
I would like to know what is the mistake I am making, or if I am using the wrong time function.
I hope I have been clear with the explanation. Thank you very much in advance for your help.

[v-rongtiep-msft · Accepted solution]

Hi @CarlosOlmos29 ,

Your first attempt at a measure seems to be using a hard-coded date of April 15th, 2023. This would only work for that specific date and would not dynamically update as new data is added.

Your second attempt at a measure seems to be using the MAX function to get the latest date from the Actuals table, but it is not clear how this is being used in the context of the measure calculation.

To calculate the measure based on the 15th day of each month, you could modify the EOMONTH function in your original measure to use the 15th day of the previous month instead. For example:

Graph_dispersion_mes =
CALCULATE (
    DIVIDE (
        SUM ( Table[Real Oil] ) - SUM ( Table[Plan Oil] ),
        SUM ( Table[Plan Oil] )
    ),
    Table[Date]
        = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 15 )
)

This would calculate the measure based on the 15th day of the previous month. You could also modify this to use a specific date if needed.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[CarlosOlmos29]

Thanks you very much! It works perfect