Forum Discussion

TrentS's avatar
TrentS
Helper IV
4 years ago
Solved

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 ...
Need Help
  • Icey's avatar
    Icey
    4 years ago

    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.

VijayP's avatar
VijayP
Community Champion
4 years ago

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

  • TrentS's avatar
    TrentS
    Helper IV
    4 years ago

    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