Hello folks,
I have a complicated dax that works perfectly on its own when I visualize it in PBI (it consists of 3 measures). But I need to show this measure in different time comparisons so this is why I copied the measures to tabular editor and put together the three measures into one calculation item. There is no bug on the calculation item page, it is saved, but PBI matrix visual shows incorrect 0 values.
Here are the measures separately in PBI (original calculations):
Valuerunningtotalbysprintwk =
CALCULATE (
[Velocity],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] )
&& 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] )
)
)
ValuerunningtotalpreviousS =
VAR previousyearlastsprintwknumber =
MAXX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC]
= MAX ( 'Calendar'[Sprint Year CC] ) - 1
),
'Calendar'[Sprint Wk CC]
)
RETURN
IF (
SELECTEDVALUE ( 'Calendar'[Sprint Wk CC] ) <> 1,
CALCULATE (
Valuerunningtotalbysprintwk,
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC]
= MAX ( 'Calendar'[Sprint Wk CC] ) - 1
&& 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] )
)
),
CALCULATE (
Valuerunningtotalbysprintwk,
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC]
= MAX ( 'Calendar'[Sprint Year CC] ) - 1
&& 'Calendar'[Sprint Wk CC] = previousyearlastsprintwknumber
&& 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] )
)
)
)
MainKPI= DIVIDE(
Valuerunningtotalbysprintwk - ValuerunningtotalpreviousS,
ValuerunningtotalpreviousS
)
I have created variants to be able to put them together to the calculated items page (tabular editor):
VAR Valuerunningtotalbysprintwk =
CALCULATE (
SELECTEDMEASURE(),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] )
&& 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] )
)
)
VAR ValuerunningtotalpreviousS =
VAR previousyearlastsprintwknumber =
MAXX (
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC]
= MAX ( 'Calendar'[Sprint Year CC] ) - 1
),
'Calendar'[Sprint Wk CC]
)
RETURN
IF (
SELECTEDVALUE ( 'Calendar'[Sprint Wk CC] ) <> 1,
CALCULATE (
Valuerunningtotalbysprintwk,
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC]
= MAX ( 'Calendar'[Sprint Wk CC] ) - 1
&& 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] )
)
),
CALCULATE (
Valuerunningtotalbysprintwk,
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Year CC]
= MAX ( 'Calendar'[Sprint Year CC] ) - 1
&& 'Calendar'[Sprint Wk CC] = previousyearlastsprintwknumber
&& 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] )
)
)
)
VAR RESULT =
DIVIDE(
Valuerunningtotalbysprintwk - ValuerunningtotalpreviousS,
ValuerunningtotalpreviousS
)
RETURN RESULT
The main purpose of this calculation is to see how the selected measure improved compared to the previous sprint, but also thinking about which day the report is open, because I want compare the actual sprint value on the latest day compared to the previous sprint with the same day running total.
If I only copy this part of the measure to tabular editor, the value shows up in PBI:
Valuerunningtotalbysprintwk =
CALCULATE (
SELECTEDMEASURE(),
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] )
&& 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] )
&& 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] )
)
)
Any help is appreciated, how I could fix the formula in editor to see the values in PBI.
Many thanks in advance!
