Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
duncfair
Advocate II
Advocate II

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 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()))
1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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 )

View solution in original post

2 REPLIES 2
d_gosbell
Super User
Super User

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
Super User
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-Calend...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.