Forum Discussion
Helper IVSQL to DAX conversion help needed
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
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 )
2 Replies
- amitchandak
Super User
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-Calendar-1-5-Power/ba-p/1187441 - d_gosbell
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 )
