topic Rolling "in this month" calculation in DAX Commands and Tips

Rolling "in this month" calculation

simonandtony — Tue, 01 Oct 2024 11:25:02 GMT

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?

Re: Rolling "in this month" calculation

dharmendars007 — Tue, 01 Oct 2024 11:46:51 GMT

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

Re: Rolling "in this month" calculation

Anonymous — Wed, 02 Oct 2024 07:37:13 GMT

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.