cancel
Showing results 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

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.

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

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

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

Announcements

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

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors