cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zhagues
New Member

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

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

zhagues_4-1674668356770.png

 

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. 

zhagues_1-1674666906318.png

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  

zhagues_5-1674668383535.png

 

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:

zhagues_6-1674668457769.png

zhagues_7-1674668475163.png

 

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.

 

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.