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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.