topic Re: Same day Last Month - Month not continuous in DAX Commands and Tips

Same day Last Month - Month not continuous

Gionedis — Fri, 31 Mar 2023 18:16:10 GMT

Hello,

I have a column with number of customers of the day, i need to compare it with the same day last month, the problem is that i don't have all days in my table, i don't have weekends and holidays, because there are no customers these days. For these days i need to find the closest day.

Ex:

day 23/03/2023 (717) i want to compare with 23/02/2023 (817)

day 22/03/2023 (773) i want to compare with 17/02/2023 (758), because I don't have 22/02/2023 so the next available day is 17/02

Thanks

Re: Same day Last Month - Month not continuous

Jihwan_Kim — Fri, 31 Mar 2023 20:12:22 GMT

Hi,

I assume you have a date dim table in the model.

Please check the below picture and the attached pbix file. I tired to create a sample pbix file like below.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Prev month value measure: = VAR _prevmonthdate = CALCULATE ( MAX ( 'Calendar'[Date] ), DATEADD ( 'Calendar'[Date], -1, MONTH ) ) VAR _alldatesbeforeprevmonthdate = ADDCOLUMNS ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] <= _prevmonthdate ), "@value", [Value measure:] ) VAR _nonblankvaluelatestdatebeforeprevmonthdate = MAXX ( FILTER ( _alldatesbeforeprevmonthdate, [@value] <> BLANK () ), 'Calendar'[Date] ) RETURN IF ( NOT ISBLANK ( [Value measure:] ), CALCULATE ( [Value measure:], 'Calendar'[Date] = _nonblankvaluelatestdatebeforeprevmonthdate ) )

Re: Same day Last Month - Month not continuous

CNENFRNL — Fri, 31 Mar 2023 20:51:25 GMT

Re: Same day Last Month - Month not continuous

Gionedis — Fri, 31 Mar 2023 21:08:01 GMT

Works like a Charm Thanks, I'll study this formula to understand in the future, Thank you, my friend