Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Fechner
Frequent Visitor

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

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:

Here is a link to download the PowerBI report:

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

1 ACCEPTED SOLUTION
Fechner
Frequent Visitor

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.

View solution in original post

3 REPLIES 3
Fechner
Frequent Visitor

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
Super User
Super User

@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.

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.