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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TrentS
Helper III
Helper III

Fiscal Quarter usage/Data Structure help

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.
quarterrank.PNG

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!

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

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

quar.png

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
TrentS
Helper III
Helper III

Good afternoon,

Still hoping to get some additional suggestions on this issue. Most appreciated!

Trent

TrentS
Helper III
Helper III

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:

MockDataMockData

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

Icey
Community Support
Community Support

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

quar.png

 

 

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

VijayP
Super User
Super User

@TrentS Try creating a Date Table with Fiscal quarter they way you have shown above . Separate Date Table will definately a solution for this!




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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