MTD calculation on the 1st of each month

Anonymous — Wed, 01 Feb 2023 19:18:38 GMT

I have multiple visualizations that show me case fill (ship qty/order qty) on my report. Many of them are filtered in the filter pane to show case fill for orders only in the current month, therefore creating a MTD metric visualization. I relaized today on the 1st of the month, no data was showing. Our data is refreshed in the monring to update the data that was put in yesterday. Since today is 2/1 but data is only loaded through 1/31, my MTD visualizations are empty.

I was trying to create a measure that would give me case fill for the last 30 days when it's the first of the month, and normal MTD case fill any other day of the month. This is what I came up with, but when I plut it inot my visuals no data is generating.

MTD CF =

IF(TODAY()=STARTOFMONTH('Calendar Table'[Date]),

CALCULATE('Case Fill Data'[Case Fill],

DATESBETWEEN('Calendar Table'[Date], PREVIOUSDAY('Calendar Table'[Date]),TODAY()-30)),

TOTALMTD('Case Fill Data'[Case Fill],'Calendar Table'[Date]))

Re: MTD calculation on the 1st of each month

djurecic — Wed, 01 Feb 2023 19:40:04 GMT

Hi Anonymous ,

It looks like you may need to add a SUM function after Calculate. Like CALCULATE(SUM('Case Fill Data'[Case Fill]) etc..

CALCULATE('Case Fill Data'[Case Fill],

topic MTD calculation on the 1st of each month in DAX Commands and Tips

MTD calculation on the 1st of each month

Re: MTD calculation on the 1st of each month