Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
hungry_learner
Frequent Visitor

Rolling Average Issues

‎12-24-2024 09:55 AM

Hi All,

 

Im trying to obtain rolling Average with the measure for below sample data:

 

DateMonthYearCountryValue
01-06-2021June2021abc0
01-07-2021July2021abc77.16
01-05-2024May2024abc213.36
01-07-2024July2024abc189.39
01-10-2024October2024abc189.39
01-11-2024November2024abc189.39
01-01-2021January2021abc0
01-02-2021February2021abc0
01-03-2021March2021abc0
01-04-2021April2021abc0
01-05-2021May2021abc0
01-08-2021August2021abc0
01-09-2021September2021abc0
01-10-2021October2021abc0
01-11-2021November2021abc0
01-12-2021December2021abc0
01-01-2022January2022abc0
01-02-2022February2022abc0
01-03-2022March2022abc0
01-04-2022April2022abc0
01-05-2022May2022abc0
01-06-2022June2022abc0
01-07-2022July2022abc0
01-08-2022August2022abc0
01-09-2022September2022abc0
01-10-2022October2022abc0
01-11-2022November2022abc0
01-12-2022December2022abc0
01-01-2023January2023abc0
01-02-2023February2023abc0
01-03-2023March2023abc0
01-04-2023April2023abc0
01-05-2023May2023abc0
01-06-2023June2023abc0
01-07-2023July2023abc0
01-08-2023August2023abc0
01-09-2023September2023abc0
01-10-2023October2023abc0
01-11-2023November2023abc0
01-12-2023December2023abc0
01-01-2024January2024abc0
01-02-2024February2024abc0
01-03-2024March2024abc0
01-04-2024April2024abc0
01-06-2024June2024abc0
01-08-2024August2024abc0
01-09-2024September2024abc0

 

Dax im using :

CALCULATE(
    AVERAGEX(
        FILTER(
            ALLSELECTED('3-Year Data'),
            '3-Year Data'[Country] = SELECTEDVALUE('3-Year Data'[Country]) &&
            '3-Year Data'[Date] <= MAX('3-Year Data'[Date]) &&
            '3-Year Data'[Date] > EDATE(MAX('3-Year Data'[Date]), -36)
        ),
        '3-Year Data'[Value]
    )
)
 
i need to show this measure into line chart but somehow when i put into line chart it doesnt show anything and only shows lines when i select atleasat one country in slicer.
 
PS -  I also tried this with calculated column and it works in line chart as well but value column is created using multiple columns from single table and further it is be used in more calculations so when i try to create further columns it shows circular dependecny error.
 
 
can you guys please guide me through this since im stuck with this. any help would be appreciated.
Labels:
Message 1 of 5
220 Views
1 ACCEPTED SOLUTION
Bibiano_Geraldo
Community Champion
Community Champion

‎12-24-2024 10:21 PM

Hi @hungry_learner ,

The issue you're facing lies in this part of your DAX formula:

SELECTEDVALUE('3-Year Data'[Country])

This function works perfectly when a single value is selected in the slicer for Country, but if no selection is made (or multiple values are selected), SELECTEDVALUE returns BLANK. This is why your line chart doesn't display any data when no country is selected—it is expecting a single country to be selected to calculate the rolling average.

 

The SELECTEDVALUE function only returns a value when exactly one item is selected in the slicer. If:

  • No selection is made, the function returns BLANK.
  • Multiple selections are made, the function also returns BLANK.

In your case, the rolling average calculation depends on a specific country being selected. When no selection is made, the formula fails to evaluate because SELECTEDVALUE is returning BLANK.

 

you can default to calculating the rolling average across all countries when no country is selected.

CALCULATE(
    AVERAGEX(
        FILTER(
            ALLSELECTED('3-Year Data'),
            (ISBLANK(SELECTEDVALUE('3-Year Data'[Country])) || 
            '3-Year Data'[Country] = SELECTEDVALUE('3-Year Data'[Country])) &&
            '3-Year Data'[Date] <= MAX('3-Year Data'[Date]) &&
            '3-Year Data'[Date] > EDATE(MAX('3-Year Data'[Date]), -36)
        ),
        '3-Year Data'[Value]
    )
)

 

I hope this helps! 
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
 Give it a like to show your appreciation!

✨ Thank you for contributing to our amazing Power BI community! 

View solution in original post

Message 3 of 5
158 Views
0
4 REPLIES 4
Ashish_Mathur
Super User
Super User

‎12-25-2024 03:49 PM

Hi,

You should first of all create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.  Create a relationship (Many to One and single) from the Date column of your Fact table to the Date column of the Calendar Table.  To your visual/slicers/filters, add Year, Month from the Calendar Table.  Modify your measure to:

Total = SUM('3-Year Data'[Value])

Measure = AVERAGEX(DATESBETWEEN(Calendar[date],MAX(Calendar[Date]),EDATE(MAX('3-Year Data'[Date]), -36)),[Total])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 5 of 5
85 Views
0
Kedar_Pande
Community Champion
Community Champion

‎12-24-2024 11:25 PM

@hungry_learner 

Rolling Average 36M = 
CALCULATE(
AVERAGEX(
FILTER(
ALL('3-Year Data'),
'3-Year Data'[Country] = SELECTEDVALUE('3-Year Data'[Country]) &&
'3-Year Data'[Date] <= MAX('3-Year Data'[Date]) &&
'3-Year Data'[Date] > EDATE(MAX('3-Year Data'[Date]), -36)
),
'3-Year Data'[Value]
)
)

Ensure that the Date column in '3-Year Data' is properly formatted as a date.

Use '3-Year Data'[Date] as the X-axis to plot the rolling average over time.

 

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
| 🌐 Connect on LinkedIn
Message 4 of 5
143 Views
0
Bibiano_Geraldo
Community Champion
Community Champion

‎12-24-2024 10:21 PM

Hi @hungry_learner ,

The issue you're facing lies in this part of your DAX formula:

SELECTEDVALUE('3-Year Data'[Country])

This function works perfectly when a single value is selected in the slicer for Country, but if no selection is made (or multiple values are selected), SELECTEDVALUE returns BLANK. This is why your line chart doesn't display any data when no country is selected—it is expecting a single country to be selected to calculate the rolling average.

 

The SELECTEDVALUE function only returns a value when exactly one item is selected in the slicer. If:

  • No selection is made, the function returns BLANK.
  • Multiple selections are made, the function also returns BLANK.

In your case, the rolling average calculation depends on a specific country being selected. When no selection is made, the formula fails to evaluate because SELECTEDVALUE is returning BLANK.

 

you can default to calculating the rolling average across all countries when no country is selected.

CALCULATE(
    AVERAGEX(
        FILTER(
            ALLSELECTED('3-Year Data'),
            (ISBLANK(SELECTEDVALUE('3-Year Data'[Country])) || 
            '3-Year Data'[Country] = SELECTEDVALUE('3-Year Data'[Country])) &&
            '3-Year Data'[Date] <= MAX('3-Year Data'[Date]) &&
            '3-Year Data'[Date] > EDATE(MAX('3-Year Data'[Date]), -36)
        ),
        '3-Year Data'[Value]
    )
)

 

I hope this helps! 
If you found this answer helpful:
✔️ Mark it as the solution to help others find it faster.
 Give it a like to show your appreciation!

✨ Thank you for contributing to our amazing Power BI community! 
Message 3 of 5
159 Views
0
v-tianyich-msft
Community Support
Community Support

‎12-24-2024 05:53 PM

Hi @hungry_learner ,

There is nothing wrong with the expression, in your logic you need to specify country by SELECTEDVALUE and then calculate the average over three years. That means it doesn't work when you don't select country. Perhaps you need to use HASONEFILTER(). Also, you need to calculate another part of the logic that needs to be displayed when unselected.
HASONEFILTER function (DAX) - DAX | Microsoft Learn

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Message 2 of 5
173 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All