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

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.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors