Rolling "in this month" calculation

[simonandtony]

I have an operations dashboard that displays data for the current month and year-to-date (YTD). On the current month side, all my visuals use a relative date filter to show items from "this month."

The data is updated daily, with the SQL build providing data up to the previous day (today -1).

The issue arises at the start of a new month—on the 1st, the reports appear blank because the filter is trying to include today's data, which isn't yet available.

How can I ensure that my "current month" visuals continue to display the full month's data without going blank on the first day?

[v-kongfanf-msft · Accepted solution]

Hi @simonandtony ,

Maybe you can try formula like below:

CurrentMonthData = 
VAR TodayDate =
    TODAY ()
VAR StartOfMonth_ =
    DATE ( YEAR ( TodayDate ), MONTH ( TodayDate ), 1 )
VAR EndOfMonth_ =
    EOMONTH ( TodayDate, 0 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        'Table'[OrderDate] >= StartOfMonth_
            && 'Table'[OrderDate] <= EndOfMonth_ - 1
    )

Best Regards,
Adamk Kong

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

[dharmendars007]

Hello @simonandtony , 

Try the below measure y replacing your measures which you have created..

CurrentMonthData =
IF(
DAY(TODAY()) = 1,
CALCULATE([YourMeasure], DATESINPERIOD('Date'[Date], TODAY(), -1, MONTH)),[YourMeasure])

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN