Help creating a measure that returns data upto a specific year/date that also works with Drilldown

[Fechner]

Hello,

I'm working with public data from the German Weather Service (DWD) for a project for my university. I have monthly data from January 1881 to March 2023 for the average air temperature, sunshine duration, and precipitation for the federal states and germany as a whole. The data is connected to a date table in which I have implemented a date hierarchy. I have created a line and stacked column chart for the column 'Germany' in the 'air_temperature_mean' table. However, as the average temperature for the year 2023 is distorted due to data only being available for three months, I aimed to develop a measure that identifies the latest year for which data for all 12 months is available, which can be used in a measure to calculate the average temperature. Consequently, I have written the following measure:

letztes meteorologisch abgeschlossenes Jahr =

var vollendeteJahre =

SELECTCOLUMNS(

    FILTER(

        ADDCOLUMNS(

            DISTINCT(

                SELECTCOLUMNS('Date',"Jahr",'Date'[Datum].[Jahr])

            ),

            "vollendetes_Jahr?",

            CALCULATE(

                COUNTX('Date','Date'[Datum]),

                'Date'[Datum]

            )=12),

        [vollendetes_Jahr?]=TRUE()

    ),

"Jahr2",

[Jahr]

)

return MAXX(

    DISTINCT(

        SELECTCOLUMNS(

            SUMMARIZE(vollendeteJahre,

             [Jahr2],

             "Max",

                MAXX(

                    vollendeteJahre,

                    [Jahr2]

                )

            ),

            "letztes_meteorologisch_abgeschlossenes_Jahr",

            [Max]

        )

    ),

    [letztes_meteorologisch_abgeschlossenes_Jahr]

)

In my opinion, this measure works as intended, but just to be sure, I asked ChatGPT to write another measure for the same purpose, as I have relatively little experience with DAX:

LastYearwithFullMonthlyData =

VAR LastFullYear =

    CALCULATE(

        MAX('Date'[Datum].[Jahr]),

        FILTER(

            ALL('Date'[Datum].[Jahr]),

            CALCULATE(

                COUNTROWS('Date'),

                ALL('Date'[Datum].[Monat]),

                'Date'[Datum].[Jahr] = EARLIER('Date'[Datum].[Jahr])

            ) = 12

        )

    )

RETURN LastFullYear

I have now written the following measure to calculate the average temperature for Germany up to 2022:

Test =

var test = [LastYearwithFullMonthlyData]

return CALCULATE(AVERAGE(air_temperature_mean[Deutschland]),FILTER('Date','Date'[Datum].[Date]<=DATE(test, 12, 1)))

Initially, when applying this measure to a line and stacked column chart with the date, it appears to be functioning correctly, as the yearly level aligns correctly. However, upon drilling down to the next level in the hierarchy, the values on the quarterly and monthly levels are incorrect, and upon expanding all values down in the hierarchy, the measure only displays data up to 1990.

Replacing the variable in the "Test" measure with the number 2022 results in everything functioning as intended, and the values aligning accurately.

Does anyone have an idea why this is the case? Thank you in advance for your help.

Here is a link to the data spreadsheet:

https://1drv.ms/x/s!ArR17ZFV9utNj2FuwzJXMlN0ihJO?e=eZXvcC

Here is a link to download the PowerBI report:

https://1drv.ms/u/s!ArR17ZFV9utNj2Lc-udqx4bLxjMI?e=S8k2tR

[Fechner · Accepted solution]

I noticed that i never posted the solution I found to my problem. But perhaps other have similar problems and can therefore benefit from the following code:

last meteorologically completed year =

var completedYears=

SELECTCOLUMNS(

    FILTER(

        ADDCOLUMNS(

            DISTINCT(

                SELECTCOLUMNS('DateTable',"year",'DateTable'[Date].[Year])

            ),

            "completed_Year?",

            CALCULATE(

                COUNTX('DateTable','DateTable'[Date].[Month])

            )=12

        ),

        [completed_Year?]=TRUE()

    ),

    "year",

    [year]

)

return DISTINCT(

    SELECTCOLUMNS(

        SUMMARIZE(

           completedYears,

             [year],

             "Max",

             MAXX(

                 completedYears,

                 [year]

                 )

        ),

        "last_meteorologically_completed_year",

        [Max]

    )

)

Note that I changed some table and column names to increase code readability.

[amitchandak]

@Fechner , I am assuming you have created a date in you table using month year and then join it using date with date table

As per me simple Avg

AVERAGE(air_temperature_mean[Deutschland])

or

Avg of month=

AVERAGEX(Values(Date[Month Year]), calculate(AVERAGE(air_temperature_mean[Deutschland])))

Should work

You can try YTD measure, if needed

YTD Sales = CALCULATEAVERAGE(air_temperature_mean[Deutschland]),DATESYTD('Date'[Date],"12/31"))

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Fechner]

Thank you for your anwser. Perhaps I was not precise enough in my previous message. The measure should calculate the average temperature from January 1881 until the latest year for which all monthly data is available, which in my case is 2022. However, once the missing monthly data for 2023 is added, it should be included in the calculation as well.

Here is a link to download the PowerBI report:

https://1drv.ms/u/s!ArR17ZFV9utNj2Lc-udqx4bLxjMI?e=S8k2tR.