Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I was wondering if it is possible to calculate YoY%, QoQ% and MoM% changes dynamically in a matrix visualization based on the date hierachy level. More generally speaking I want to calculate the change of a column based on its left neighbour column.
Let's assume I have a data model like:
And a matrix like:
Is there a way to calculate a dynamic measure that calculates the corresponding % changes depending on the hierarchy level?
Thanks for your help!
Hi @mwegener Is it possible to extend this measure to work with Week over Week comparisons? I tried to do the following, but it doesn't seem to work - would appreciate your insights:
Note: dim_weeks[id] is a specially created table linked to my date table that increments by 1 every week.
Measure PoP% =
VAR __PREV_YEAR = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, YEAR))
VAR __YOY = DIVIDE(SUM('Fact'[Measure]) - __PREV_YEAR, __PREV_YEAR)
VAR __PREV_QUARTER = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, QUARTER))
VAR __QOQ = DIVIDE(SUM('Fact'[Measure]) - __PREV_QUARTER, __PREV_QUARTER)
VAR __PREV_MONTH = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, MONTH))
VAR __MOM = DIVIDE(SUM('Fact'[Measure]) - __PREV_MONTH, __PREV_MONTH)
VAR __PREV_WEEK = CALCULATE(SUM('Fact'[Measure]), FILTER(ALL('dim_weeks'), dim_weeks[id] = min(dim_weeks[id]) - 1)
VAR __WOW = DIVIDE(SUM('Fact'[Measure]) - __PREV_WEEK, __PREV_WEEK)
RETURN
SWITCH(TRUE(),
HASONEFILTER(Date_Dimension[Week]), __WOW,
HASONEFILTER(Date_Dimension[Month]), __MOM,
HASONEFILTER(Date_Dimension[Quarter]), __QOQ,
HASONEFILTER(Date_Dimension[Year]), __YOY,
BLANK()
)
Hi @mase_53 ,
does this solution work for you?
Measure PoP% =
VAR __PREV_YEAR = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, YEAR))
VAR __YOY = DIVIDE(SUM('Fact'[Measure]) - __PREV_YEAR, __PREV_YEAR)
VAR __PREV_QUARTER = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, QUARTER))
VAR __QOQ = DIVIDE(SUM('Fact'[Measure]) - __PREV_QUARTER, __PREV_QUARTER)
VAR __PREV_MONTH = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, MONTH))
VAR __MOM = DIVIDE(SUM('Fact'[Measure]) - __PREV_MONTH, __PREV_MONTH)
VAR __PREV_WEEK = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -7, DAY))
VAR __WOW = DIVIDE(SUM('Fact'[Measure]) - __PREV_WEEK, __PREV_WEEK)
RETURN
SWITCH(TRUE(),
HASONEFILTER(Date_Dimension[Week]), __WOW,
HASONEFILTER(Date_Dimension[Month]), __MOM,
HASONEFILTER(Date_Dimension[Quarter]), __QOQ,
HASONEFILTER(Date_Dimension[Year]), __YOY,
BLANK()
)
Hi @Anonymous ,
you can check which level is filtered and return the corresponding calculation.
Measure PoP% =
VAR __PREV_YEAR = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, YEAR))
VAR __YOY = DIVIDE(SUM('Fact'[Measure]) - __PREV_YEAR, __PREV_YEAR)
VAR __PREV_QUARTER = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, QUARTER))
VAR __QOQ = DIVIDE(SUM('Fact'[Measure]) - __PREV_QUARTER, __PREV_QUARTER)
VAR __PREV_MONTH = CALCULATE(SUM('Fact'[Measure]), DATEADD('Date_Dimension'[Date], -1, MONTH))
VAR __MOM = DIVIDE(SUM('Fact'[Measure]) - __PREV_MONTH, __PREV_MONTH)
RETURN
SWITCH(TRUE(),
HASONEFILTER(Date_Dimension[Month]), __MOM,
HASONEFILTER(Date_Dimension[Quarter]), __QOQ,
HASONEFILTER(Date_Dimension[Year]), __YOY,
BLANK()
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |