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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Bokazoit
Responsive Resident
Responsive Resident

Can this be done in Power BI?

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.

2 REPLIES 2
v-shex-msft
Community Support
Community Support

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&" '"&param&"'"])
in
    Source
in
    SQLSource

 

And use invoke to call the function.

Capture.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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