A measure to return previous month's last banking day

[GoingIncognito]

Hi. I've a calendar table that has all the usual columns and a column "IsBankingDay" that has value 1 for days that banks are open and otherwise 0. Now, how do I return in a measure what is previous months last last banking day? 

Thank you so much in advance!

[GoingIncognito]

Unfortunately neither of your solutions work.
Tex's solutions problem is propably the ifs. Conditional logic returns string not dates or integers.

Both of Amit's solutions returns me blanks.

And yes the calendar table is of the proper kind. It's calculated with M, a modified version of Radacad's script.

[amitchandak]

@GoingIncognito ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[GoingIncognito]

Thank you Amit. I could share pictures of my date table, but it's the same that Radacad uses. Just throw in there an additional column that is the IsBankingDay. 

[GoingIncognito · Accepted solution]

So this worked:

var BeginningOfCurrentMonth = DATE( YEAR(TODAY() ), MONTH(TODAY()), 1)
return
CALCULATE( MAX(Kalenteri[Date]), FILTER(Kalenteri, Kalenteri[ISBANKINGDATE] = 1 && Kalenteri[Date] < BeginningOfCurrentMonth ) )

It's not pretty - but it works. So. Yeah, that's the kind of a man I man. A mouse really.

Thank you all for participating!

[amitchandak]

@GoingIncognito , assuming that part of date table or table joined to date table

CALCULATE(Max('Date'[Date]),DATESMTD(dateadd('Date'[Date],-1,MONTH)) ,'Date'[Date][IsBankingDay]=1)

CALCULATE(Max('Date'[Date]),previousmonth('Date'[Date]) ,'Date'[Date][IsBankingDay]=1)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[tex628]

I would try something along the lines of:

Last banking day PM =
VAR today = TODAY() 
VAR tYear = YEAR(Today)
VAR tMonth = MONTH(Today)
VAR mYear = IF(tMonth = 1 ; tYear -1 ; tYear)
VAR mMonth = IF(tMonth = 1 ; 12 ; tMonth -1)  
Return
Calculate(
MAX(Calendar[Date]);
ALL(Calendar);
Calendar[BankingDay] = 1; 
Calendar[Year] = mYear;
Calendar[Month] = mMonth
) 
 

/ J