Filter data for a specific day of the month

[Syndicate_Admin]

Hello Power BI community.

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

Grafico_dispersion_mes = CALCULATE(DIVIDE(sum(Board[Real Oil])-sum(Board[Plan Oil]),sum(Board[Plan Oil])),Board[Date]=EOMONTH(TODAY(),-1))

Now the data is uploaded with the 15th 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 has worked for me:

1. Grafico_dispersion_mes = CALCULATE(DIVIDE(SUM(Real[OIL])-SUM(Plan[OIL]),SUM(Plan[OIL])), Date[Date]=DATE(2023,04,15)).

2. Grafico_dispersion_mes = CALCULATE(DIVIDE(SUM(Real[OIL])-SUM(Plan[OIL]),SUM(Plan[OIL])), Date[Date]=max(real[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.

[Anonymous · Accepted solution]

Hi  @Syndicate_Admin ,

Here are the steps you can follow：

1. Create measure.

The last day of the previous month for the current date in the table:

Last Month =
var _plan=
SUMX(
    FILTER(ALL('Board'),
    'Board'[Date]=
MAXX(FILTER(ALL(Board),MONTH('Board'[Date])=MONTH(MAX('Board'[Date]))),[Date])),[Plan Oil])
var _Real=
SUMX(
    FILTER(ALL('Board'),
    'Board'[Date]=
MAXX(FILTER(ALL(Board),MONTH('Board'[Date])=MONTH(MAX('Board'[Date]))),[Date])),[Real Oil])
return
DIVIDE(
    _plan,_Real)

Today is the last day of last month

Last Month Today =
 var _plan=
SUMX(
    FILTER(ALL('Board'),
    'Board'[Date]=
MAXX(FILTER(ALL(Board),MONTH('Board'[Date])=MONTH(TODAY())-1),[Date])),[Plan Oil])
 var _Real=
SUMX(
    FILTER(ALL('Board'),
    'Board'[Date]=
MAXX(FILTER(ALL(Board),MONTH('Board'[Date])=MONTH(TODAY())-1),[Date])),[Real Oil])
return
DIVIDE(_plan,_Real)

2.  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