SQL to DAX conversion help needed

[duncfair]

Here is the SQL

SELECT sum(v.isShippingDay)
FROM dbo.vw_FiscalCal v
WHERE DATEPART(YEAR, [fullDate]) = DATEPART(YEAR, GETDATE())
AND v.[FiscalMonthofYear] = (SELECT [FiscalMonthofYear] FROM dbo.vw_FiscalCal WHERE [fullDate] = CAST(GETDATE() AS DATE))

Current (wrong) formula: doesn't work as it assumes the current calendar month = current fiscal month

Days in Month = calculate(SUM(vw_FiscalCal[isShippingDay]),YEAR(vw_FiscalCal[fullDate]) = YEAR(TODAY()), MONTH(vw_FiscalCal[fullDate]) = MONTH(TODAY()))

[d_gosbell · Accepted solution]

It looks to me like the reply from @amitchandak might return the same result as your current expression. You did not mention this explicitly in your question, but are you possibly using a fiscal calendar that does not align with calendar months? ( like a 4-4-5 calendar or similar variant)

If so you might find the following is closer to your original SQL. Basically I've taken that missing logic which is in a subquery in your SQL and captured that value in the _fiscalMonthOfYear variable as the start of the expression and then I use that later in the final CALCULATE call (don't worry about the MAX aggregate in the variable, it should be doing the MAX of a single row)

Days in Month =
var _fiscalMonthOfYear = CALCULATE( MAX( vw_FiscalCal[FiscalMonthofYear]), vw_FiscalCal[fullDate] = TODAY())
return calculate(SUM(vw_FiscalCal[isShippingDay]),YEAR(vw_FiscalCal[fullDate]) = YEAR(TODAY()), vw_FiscalCal[FiscalMonthofYear] = _fiscalMonthOfYear )

[amitchandak]

@duncfair ,In case you need a column

new column =

sumx(filter(vw_FiscalCal , eomonth(vw_FiscalCal [Date], 0) = eomonth(today(),0)),[isShippingDay])

In case you need a measure

I think in this case you have to create a fiscal calendar and filter this month of data. the calendar should be joined with date(without time) of fact

then you can do

calculate(sum(vw_FiscalCal [isShippingDay]), filter(Date, eomonth(Date[Date], 0) = eomonth(today(),0))

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...