Fiscal period calculations Y/Y, Q/Q, M/M resulting in values of 0 when losing Date Hierarchy

[zhagues]

Hi,

1. I am working with a data set of the following nature with month-time buckets and revenues for those months grouped by Region. See below for Image A

2. I create the my M/M, Q/Q, and Y/Y measures with the Quick Measure function selecting appropriate functions. When I run M/M, Q/Q, and Y/Y calculations, I obtain a nice result. I can plot these variances over a line chart and it looks great. 

3. I now need to show this data in terms of my company's fiscal periods. Our Fiscal Year runs July (M1) to June (M

12). With my lack of DAX expertise, I manually created a Fiscal Calendar Table to relate CY to FY. See below for Image B  

4. When I create a relationship from Image A to Image B by Date, I lose the Date Hierarchy established from Table A as it transfers to Table B with a Many to One relationship. With losing the Date Hierarchy, the M/M, Q/Q, and Y/Y calculations lose their functionality and they data output when using a Fiscal period X-axis shows 0 for all values. See below for Image C example:

Ultimately, I want to show M/M, Q/Q, and Y/Y variances but with an X-axis that displays the related Fiscal period. I appreciate any and all help on this subject as it stumped me for quite some time. 

Thank you and please let me know of anything else you may need to troubleshoot.

[Greg_Deckler]

@zhagues What are your measure formulas? Also I don't understand the many to one if the relationship is built on Date columns.

[zhagues]

Hi @Greg_Deckler 

Thanks for your attention. I have listed my M/M formula below. All are similar but I have Q/Q and Y/Y as well, appended below in case you'd like to see those as well. The Many to One is being applied because in my dataset I will have a variable such as Revenue in a column but each time bucket (month) has multiple rows because of different business units. (See example of United States, Canada, and Mexico in original post therefore creating many rows for 1 time bucket) Hope this helps. 

M/M 

Total DC GP$ MoM% =

IF(

    ISFILTERED('DCdata'[Period]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_MONTH =

        CALCULATE(

            SUM('DCdata'[Total DC GP$]),

            DATEADD('DCdata'[Period].[Date], -3, MONTH)

        )

    RETURN

        DIVIDE(

            SUM('DCdata'[Total DC GP$]) - __PREV_MONTH,

            __PREV_MONTH

        )

)

Q/Q 

Total DC GP$ QoQ% =

IF(

    ISFILTERED('DCdata'[Period]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_QUARTER =

        CALCULATE(

            SUM('DCdata'[Total DC GP$]),

            DATEADD('DCdata'[Period].[Date], -1, QUARTER)

        )

    RETURN

        DIVIDE(SUM('DCdata'[Total DC GP$]) - __PREV_QUARTER, __PREV_QUARTER)

)

Y/Y

Total DC GP$ YoY% =

IF(

    ISFILTERED('DCdata'[Period]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR =

        CALCULATE(

            SUM('DCdata'[Total DC GP$]),

            DATEADD('DCdata'[Period].[Date], -1, YEAR)

        )

    RETURN

        DIVIDE(

            SUM('DCdata'[Total DC GP$]) - __PREV_YEAR,

            __PREV_YEAR

        )

)