Forum Discussion

duncfair's avatar
duncfair
Icon for Helper IV rankHelper IV
5 years ago
Solved

SQL 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 db...
Need Help
  • d_gosbell's avatar
    d_gosbell
    5 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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