Using DAX (GENERATESERIES) to show current month's values as at yesterday

[JumoGra]

Hi There,

I have a calendar table where I'm trying to create a column that shows the current month as at yesterday.  I have managed to create a DAX function, but this unfortunately includes today's date which isn't what I need as shown below in my code.  I'd appreciate if I could get some help with this please.

ThisMonth =

var _maxdate = MAX(CDCalendar[CallDate])

return

IF(DATEDIFF('CDCalendar'[CallDate], _maxdate,MONTH) in GENERATESERIES(0,0),1,0)

[JumoGra · Accepted solution]

This worked! Thank you so much for your help with this much appreciated!

[Ahmedx]

pls try this code

ThisMonth =
VAR _StartDate = EOMONTH(TODAY(),-1)+1
VAR _EndDate = TODAY()-1

return

IF('CDCalendar'[CallDate]>=_StartDate&&'CDCalendar'[CallDate]<=_EndDate,1,0)

[JumoGra · Accepted solution]

This worked! Thank you so much for your help with this much appreciated!

[Ahmedx · Accepted solution]

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

[gmsamborn]

Hi @JumoGra 

I added a column to my Date table to display yesterday's month.

Yesterday's Month = MONTH( [Date] - 1 )

I hope I understood.  Let me know if you have any questions.

Display yesterdays MONTH.pbix

[JumoGra]

Hi sorry this doesn't work, I'm trying to show all date values in the current month excluding today

[gmsamborn]

Hi @JumoGra 

I would like to help but I'm a little unclear about your requirements.

Can you provide the following?

1)  Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

3) Please explain how you would expect to get from step 1 to 2.

4) If possible, please show your past attempts at a solution.

A .pbix file with sample data would be best.

[JumoGra]

I've included some of the date columns I'm working with below, basically I need the column 'This Month' to show all values from 1st-15th Mar'24 except today's date i.e. the 16/03/2024 as I have managed to do the same for columns 'Last7Days and yesterday'

The DAX code below gives me all date values from 1st-16th Mar'24, I want today's date (16/03/24) excluded.

ThisMonth =

var _maxdate = MAX(CDCalendar[CallDate])

return

IF(DATEDIFF('CDCalendar'[CallDate], _maxdate,MONTH) in GENERATESERIES(0,0),1,0)

[gmsamborn]

Have you tried subtracting 1 from the date in your variable in _maxdate?

var _maxdate = MAX(CDCalendar[CallDate])- 1