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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Quarter + Year Text field to date field AND QoQ/YoY calculation

Hello,

 

I am trying to find a solution to my problem. I will try to explain it as detailed as possible.

 

1. I have tried to link my uploaded Excel file to a date table. The Excel file contains the following "time dimension", but it is recognized in powerbi as text format:

!NB note that there is more than one operator, meaning the quarters are repeated through the table.

 

POWERBI 1.PNG

This is linked to a datetable with a cardinality relationship many-to-many through a custom Quarter+Year colum in the datetable:

Powerbi2.jpg

My first question is: Is this a correct of linking the two table, and if not what is best practice to do this?

 

Second (which might be a consequence of not using best practice in first topic) is: when I try to calculate QoQ or YoY I am running into problems.

 

I am trying to use a DATEADD function as this seems to be recommended several times on this community.

when calculating Last quarter im using this dax formula:

LastQ = CALCULATE([Value],DATEADD('Date'[Date],-1,QUARTER) )

 and last year:

LY = CALCULATE(sum('2 2 1 Pivot Data (2)'[Værdi]), DATEADD('Date'[Date],-4, QUARTER))

when putting the measures into a table with my original values I dont get any values in my table only when it sums up, meaning I can not make a measure calculating the difference between the quarters, I have tried it and it did not work. I am stuck and would very much appreciate if anyone have had the same issues and can help!

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Join is not correct. If you do not have date create one with help from Qtr year

 

Check my video for DAX option -https://youtu.be/yPQ9UV37LOU

 

Or Create a qtr Year table with YYYYQ as key on month side and use that for time intelligence

 

new column in Qtr year table

 

Qtr Rank = RANKX(all('Date'),'Date'[Qtr Name],,ASC,Dense) //YYYYQ

 

refer: Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

 

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Anonymous
Not applicable

Thank you for your almost Immediate response! I will look into this and give it a try. Truth is, I am very new to powerbi, so many things which are very basic for others are hard for me.

Will return when I have tried your solution!

 

Good day

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors