I have a table with both current and expected data for different things that go through phases. I created an 'Is' (ist in german) measure to calculate the sum of metric for current phase, 'should' (soll in german) measure for the same but for next phase.
What I wanted to achieve is a measure with the actual forcasted total, which would mean, the measure of current data, plus the expected data, minus the data expected to advance to next phase. This would mean, for example, ist3 + soll3 - soll4, but because my current data (the ist) only reaches up to current date, everything after tends to negatives, to solve that I wanted to create another measure that sums the last months data to create the forecasted data of this month. This is what HG Z tries to do, and this is where I got stuck, cause the measures don't seam to work at all.
I asked chatGPT and asked me to debug with an altered measure, but it just helped me realize that the measures arent displaying anything when used with the HG Z (chatGPT formula at the end). Any help with this would be greatly appreciated. I must mention, all other measures work and display as expected, it's just the last one I made that doesn't work (the 'HG # Z')
Measure Formulas:
HG 3 ist =
CALCULATE(
TOTALYTD(SUM('Mastertabelle'[HG3_Hilf]), 'Dim Date'[Datum].[Date]),
USERELATIONSHIP('Mastertabelle'[Datum_Ist], 'Dim Date'[Datum])
)
-----------------------------
HG 3 soll =
TOTALYTD(SUM('Mastertabelle'[HG3_Soll]), 'Dim Date'[Datum].[Date])
-----------------------------
HG 3 Z =
VAR CurrentMonth = MONTH(TODAY())
VAR PreviousMonthHG3Ist = IF(
CurrentMonth = 1,
0, -- Handle January case by setting the previous month value to 0
CALCULATE(
[HG 3 ist] + [HG 3 soll],
DATEADD('Dim Date'[Datum], -1, MONTH)
)
)
RETURN
PreviousMonthHG3Ist
-----------------------------
HG 3 Gesamt = [HG 3 Z] + [HG 3 soll] - [HG 4 soll]
-----------------------------
For more context, my table hase columns similar to the following:
HG (the current phase of the item 1-5)
HG Soll (this is just 'HG + 1' if HG<5, so just next phase)
RPJ (our metric that we sum in the measures after further transforming with the hg#_hillf)
Expected dates (a column with the expected date for each phase, both past and future)
Expected column (column with date of next expected phase, meaning of hg soll) Its the active default relation with dim date
Actuall dates (a column for the actuall date of the reached phase)
Actual column (column with date of latest achieved phase, meaningn hg) its the one that HG 3 Ist uses for relation
-----------------------------
ChatGPT debugging that showed measures return nothing:
HG 3 Z Debug =
VAR CurrentMonth = MONTH(TODAY())
VAR PreviousMonthHG3Ist =
IF(
CurrentMonth = 1,
0,
CALCULATE(
[HG 3 ist] + [HG 3 soll],
DATEADD('Dim Date'[Datum], -1, MONTH)
)
)
RETURN
CONCATENATEX(
VALUES('Dim Date'[Datum]),
'Dim Date'[Datum] & ": HG 3 ist = " & FORMAT([HG 3 ist], "#,##0") & ", HG 3 soll = " & FORMAT([HG 3 soll], "#,##0") & ", PreviousMonthHG3Ist = " & FORMAT(PreviousMonthHG3Ist, "#,##0"),
", "
)
