topic Re: Quarter + Year Text field to date field AND QoQ/YoY calculation in DAX Commands and Tips

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

Anonymous — Wed, 03 Mar 2021 12:39:10 GMT

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!

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

amitchandak — Wed, 03 Mar 2021 12:54:19 GMT

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

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

Anonymous — Wed, 03 Mar 2021 13:12:50 GMT

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