Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I use this code in SSRS whenever the report is run
declare @SlDto date = '20151231' select Period, case when (case when DATEPART(YY,dateadd(mm,0,@SlDto))-DATEPART(YY,dateadd(mm,-2,@SlDto)) > 0 then 1 else 0 end) = 0 then cast(YEAR(Period) AS varchar(4)) else cast( year(@SlDto)-1+(case when MONTH(Period) between 1 and MONTH(@SlDto) then DATEDIFF(YY,@SlDto,Period) else DATEDIFF(YY,@SlDto,Period) +1 end) as varchar(4))+ '/'+cast( year(@SlDto)+(case when MONTH(Period) between 1 and MONTH(@SlDto) then DATEDIFF(YY,@SlDto,Period) else DATEDIFF(YY,@SlDto,Period) +1 end) as varchar(4)) end as yy, case when DATEPART(mm,Period) in (DATEPART(MM,dateadd(mm,0,@SlDto)), DATEPART(MM,dateadd(mm,-1,@SlDto)), DATEPART(MM,dateadd(mm,-2,@SlDto))) then 4 when DATEPART(mm,Period) in (DATEPART(MM,dateadd(mm,-3,@SlDto)), DATEPART(MM,dateadd(mm,-4,@SlDto)), DATEPART(MM,dateadd(mm,-5,@SlDto))) then 3 when DATEPART(mm,Period) in (DATEPART(MM,dateadd(mm,-6,@SlDto)), DATEPART(MM,dateadd(mm,-7,@SlDto)), DATEPART(MM,dateadd(mm,-8,@SlDto))) then 2 when DATEPART(mm,Period) in (DATEPART(MM,dateadd(mm,-9,@SlDto)), DATEPART(MM,dateadd(mm,-10,@SlDto)), DATEPART(MM,dateadd(mm,-11,@SlDto))) then 1 end as Qrt, DATEPART(mm,Period) as mth,
Depending on the date @SlDto it calculates new Quarters and Years. It is not ordinary Year and Quarter calculations.
I hope that this can be done in Power BI depeding on a slicer choice.
Hi @Bokazoit,
Yes, it is possible. You can package these query to a stored procedures then use power query to invoke, sample:
let SQLSource = (SPName as text, param as text) => let Source = Sql.Database("xxxxxx", "xxxxx", [Query="exec "&SPName&" '"¶m&"'"]) in Source in SQLSource
And use invoke to call the function.
Limitations:
1. Only works on query editor, not support visuals and reports.
2. Not support "direct query" mode.
3. Only works on power bi desktop, power bi service not support this feature.
Regards,
Xiaoxin Sheng
Ok well that is not a great solution. Either I need to rethink this or just don't do it, unless somebody else tells it can be done in here 🙂
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |