CharlotteWeather

CALCULATE(
    AVERAGE('Observation'[Mean Temperature]),
    REMOVEFILTERS(),
    'Date'[Is Anomaly Baseline] = TRUE
)

VAR _Baseline = [Baseline Mean Temperature 1940 To 1980]
VAR _Current = AVERAGE('Observation'[Mean Temperature])
RETURN
    IF(NOT ISBLANK(_Current), _Current - _Baseline)

VAR _Min = CALCULATE(MIN('Observation'[Date]), REMOVEFILTERS())
VAR _Max = CALCULATE(MAX('Observation'[Date]), REMOVEFILTERS())
RETURN
    FORMAT(_Min, "mmmm d, yyyy") & " to " & FORMAT(_Max, "mmmm d, yyyy")

VAR _Rolling = [Rolling 12 Month Precipitation]
VAR _Normal = [Normal Annual Precipitation]
VAR _MonthsCovered =
    CALCULATE(
        DISTINCTCOUNT('Date'[Year Month]),
        DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
    )
RETURN
    IF(_MonthsCovered = 12, DIVIDE(_Rolling, _Normal))

VAR _Pct = [Percent Of Normal Precipitation] * 100
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(_Pct), "No Data",
        _Pct < 50, "Exceptional Drought (D4)",
        _Pct < 65, "Extreme Drought (D3)",
        _Pct < 75, "Severe Drought (D2)",
        _Pct < 85, "Moderate Drought (D1)",
        _Pct < 100, "Abnormally Dry (D0)",
        "Near or Above Normal"
    )

VAR _Actual = [Trailing 12 Month Precipitation]
VAR _Normal = [Normal Annual Precipitation]
VAR _Deficit = [Precipitation Deficit]
VAR _PctBelow = DIVIDE(_Deficit, _Normal) * 100
VAR _AnchorDate = [Last Observation Date]
RETURN
    "The 12 months ending " & FORMAT(_AnchorDate, "mmmm d, yyyy")
    & " received " & FORMAT(_Actual, "0.00") & " inches against a 1991-2020 normal of "
    & FORMAT(_Normal, "0.00") & " inches - a deficit of " & FORMAT(_Deficit, "0.00")
    & " inches, or " & FORMAT(_PctBelow, "0") & "% below normal. Current classification: "
    & [Drought Classification] & "."

VAR _Normal = [Normal Annual Precipitation]
VAR _Actual = [Trailing 12 Month Precipitation]
RETURN
    _Normal - _Actual

VAR _Anchor = [Last Observation Date]
VAR _WindowStart = EOMONTH(_Anchor, -12) + 1
VAR _Months = VALUES('Date'[Month Number])
RETURN
    CALCULATE(
        SUM('Observation'[Precipitation]),
        REMOVEFILTERS(),
        'Date'[Date] >= _WindowStart,
        'Date'[Date] <= _Anchor,
        TREATAS(_Months, 'Date'[Month Number])
    )

VAR _AnchorDate = [Last Observation Date]
VAR _WindowStart = EOMONTH(_AnchorDate, -12) + 1
RETURN
    CALCULATE(
        SUM('Observation'[Precipitation]),
        REMOVEFILTERS(),
        'Date'[Date] >= _WindowStart,
        'Date'[Date] <= _AnchorDate
    )

VAR _AnchorDate = [Last Observation Date]
VAR _AllDays =
    CALCULATETABLE(
        SUMMARIZE('Observation', 'Observation'[Date], 'Observation'[Precipitation]),
        REMOVEFILTERS(),
        'Date'[Date] <= _AnchorDate
    )
VAR _WithReadings = FILTER(_AllDays, NOT ISBLANK('Observation'[Precipitation]))
VAR _LastWetDate =
    MAXX(FILTER(_WithReadings, 'Observation'[Precipitation] >= 0.01), 'Observation'[Date])
RETURN
    COUNTROWS(FILTER(_WithReadings, 'Observation'[Date] > _LastWetDate))

VAR _DryDays =
    FILTER(
        CALCULATETABLE(
            SUMMARIZE('Observation', 'Observation'[Date], 'Observation'[Precipitation])
        ),
        NOT ISBLANK('Observation'[Precipitation]) && 'Observation'[Precipitation] < 0.01
    )
VAR _Ranked =
    ADDCOLUMNS(
        _DryDays,
        "@Grp", INT('Observation'[Date]) - RANKX(_DryDays, INT('Observation'[Date]), , ASC, DENSE)
    )
VAR _Streaks =
    GROUPBY(_Ranked, [@Grp], "@Len", COUNTX(CURRENTGROUP(), 1))
RETURN
    MAXX(_Streaks, [@Len])

VAR _Current = [Trailing 12 Month Precipitation]
VAR _Complete = CALCULATETABLE('Rolling Window', REMOVEFILTERS())
RETURN
    IF(
        NOT ISBLANK(_Current),
        COUNTROWS(FILTER(_Complete, 'Rolling Window'[Window Precipitation] < _Current)) + 1
    )

VAR _Rank = [Driest 12 Month Period Rank]
VAR _Total = [Complete Window Count]
VAR _Pctile = DIVIDE(_Rank, _Total)
RETURN
    IF(
        NOT ISBLANK(_Rank),
        "Ranks " & FORMAT(_Rank, "#,##0") & " driest of " & FORMAT(_Total, "#,##0")
        & " complete twelve-month windows since 1940 — drier than " & FORMAT(1 - _Pctile, "0%") & " of the record."
    )

VAR _BaselineByMonthYear =
    CALCULATETABLE(
        SUMMARIZE('Date', 'Date'[Year], 'Date'[Month Number]),
        REMOVEFILTERS(),
        'Date'[Is Normals Baseline] = TRUE
    )
VAR _MonthlyTotals =
    ADDCOLUMNS(_BaselineByMonthYear, "@Total", CALCULATE(SUM('Observation'[Precipitation])))
VAR _ByMonth =
    GROUPBY(_MonthlyTotals, 'Date'[Month Number], "@MonthNormal", AVERAGEX(CURRENTGROUP(), [@Total]))
RETURN
    SUMX(_ByMonth, [@MonthNormal])

VAR _MonthNumbers = VALUES('Date'[Month Number])
VAR _BaselineByMonthYear =
    CALCULATETABLE(
        SUMMARIZE('Date', 'Date'[Year], 'Date'[Month Number]),
        REMOVEFILTERS(),
        'Date'[Is Normals Baseline] = TRUE,
        TREATAS(_MonthNumbers, 'Date'[Month Number])
    )
VAR _MonthlyTotals =
    ADDCOLUMNS(_BaselineByMonthYear, "@Total", CALCULATE(SUM('Observation'[Precipitation])))
VAR _ByMonth =
    GROUPBY(_MonthlyTotals, 'Date'[Month Number], "@MonthNormal", AVERAGEX(CURRENTGROUP(), [@Total]))
RETURN
    SUMX(_ByMonth, [@MonthNormal])

VAR _LastDate = MAX('Date'[Date])
RETURN
    CALCULATE(
        SUM('Observation'[Precipitation]),
        DATESINPERIOD('Date'[Date], _LastDate, -12, MONTH)
    )

VAR _Rolling = [Rolling 12 Month Precipitation]
VAR _Normal = [Normal Annual Precipitation]
RETURN
    IF(NOT ISBLANK(_Rolling), _Rolling - _Normal)