Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 )
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 )
@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...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |