cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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.

2 REPLIES 2
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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
New Member

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\$]),
)
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\$]),
)
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\$]),
)
RETURN
DIVIDE(
SUM('DCdata'[Total DC GP\$]) - __PREV_YEAR,
__PREV_YEAR
)
)

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors