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
Bokazoit
Post Patron
Post Patron

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
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.