Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Greetings all,
I need some theory/ structure help.
I have a data set in which a text based column for a custom fiscal quarter is given (and the sponsors wish to have it remain in this format) is like 21Q3. It also has a publish date that is PRIOR to said Fiscal quarter. (It's a forecasting number.)
The sponsors wish to have columns showing both current and prior quarter numbers and a series of Quarter-over-Quarter Delta calculations.
My initial thought was to create a table with the custom 'Q' formatting and add a rank column to be able to use DAX coding where Rank-1 would give me the previous quarter data using integer values. I was able to create relational columns in the primary data table for current and previous quarter ranks in order to remove the need to call the relationship within each delta measure.
I have not been able to make that work.
I also added a column for the first day of a quarter with a date format in hopes of being able to use time intelligence features. but given that the date in the data table is for the previous quarter and is inconsistent, it has also caused an issue.
So....how would you address this problem? What do I need to do to be able to get a measure that gives me the q-over-q change of something like a forecasted price with the current data given and displayed under a text based quarter column?
Please note that I am certainly thankful for your input here and if you are feeling crafty with your DAX, I would certainly take that as a learning opportunity!
Solved! Go to Solution.
Hi @TrentS ,
I didn't use the quarter rank. Please check if my method meet your requirement:
Measure =
VAR CurrentProduct_ =
MAX ( 'Data Table'[Product] )
VAR CurrentRetailer_ =
MAX ( 'Data Table'[Retailer] )
VAR CurrentCountry_ =
MAX ( 'Data Table'[Country] )
VAR CurrentQuar_ =
MAX ( 'Data Table'[Quarter] )
VAR PreviousQuar_ =
CALCULATE (
MAX ( 'Data Table'[Quarter] ),
FILTER (
ALL ( 'Data Table' ),
'Data Table'[Product] = CurrentProduct_
&& 'Data Table'[Retailer] = CurrentRetailer_
&& 'Data Table'[Country] = CurrentCountry_
&& 'Data Table'[Quarter] < CurrentQuar_
)
)
RETURN
CALCULATE (
SUM ( 'Data Table'[Price] ),
FILTER (
ALL ( 'Data Table' ),
'Data Table'[Product] = CurrentProduct_
&& 'Data Table'[Retailer] = CurrentRetailer_
&& 'Data Table'[Country] = CurrentCountry_
&& 'Data Table'[Quarter] = PreviousQuar_
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Good afternoon,
Still hoping to get some additional suggestions on this issue. Most appreciated!
Trent
Let me approach this another way. I have a Date table with a relationship between QUARTER columns to the data table. This is a TEXT field but obviously ties to date format fields in the Date table. The table in the original post is also in the model for the ranks and First date of quarter.
I have a data table thats looks like this:
I need to do a quarter-over-quarter comparison on the price change betwwen Q3 and Q4 based on the GREEN header columns. (Version and Country Code are in the data but not needed.) The rank columns correspond to the table in the original post.
(I have also created a concat column on the Product/Retailer/Country/Quarter if that is easier to use.)
What is the best approach here? FILTER/ALLExcept statements?
Thanks in advance for your time,
Trent
Hi @TrentS ,
I didn't use the quarter rank. Please check if my method meet your requirement:
Measure =
VAR CurrentProduct_ =
MAX ( 'Data Table'[Product] )
VAR CurrentRetailer_ =
MAX ( 'Data Table'[Retailer] )
VAR CurrentCountry_ =
MAX ( 'Data Table'[Country] )
VAR CurrentQuar_ =
MAX ( 'Data Table'[Quarter] )
VAR PreviousQuar_ =
CALCULATE (
MAX ( 'Data Table'[Quarter] ),
FILTER (
ALL ( 'Data Table' ),
'Data Table'[Product] = CurrentProduct_
&& 'Data Table'[Retailer] = CurrentRetailer_
&& 'Data Table'[Country] = CurrentCountry_
&& 'Data Table'[Quarter] < CurrentQuar_
)
)
RETURN
CALCULATE (
SUM ( 'Data Table'[Price] ),
FILTER (
ALL ( 'Data Table' ),
'Data Table'[Product] = CurrentProduct_
&& 'Data Table'[Retailer] = CurrentRetailer_
&& 'Data Table'[Country] = CurrentCountry_
&& 'Data Table'[Quarter] = PreviousQuar_
)
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Icey,
Thank you for your efforts here including the pbix! It looks like I may have been structuring my Filters incorrectly. In any case, I will have to test how it wil scale with additional quarterly data and how the quarterly filters react but this gives me a new look at how to approach it. Giving me the column of the "prior" quarter based on the filter columns allows for that simple calculation.
Many thanks,
Trent
@TrentS Try creating a Date Table with Fiscal quarter they way you have shown above . Separate Date Table will definately a solution for this!
Proud to be a Super User!
Thanks VijayP.
I have created a custom Date table previously adding in the "short" version of the quarter.
I did also try some alternative Date Tables (such as from EnterpriseDNA) just to see if there was additional value but ultimately just went back to the custom one and the 'rank' table above.
Trent
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |