Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I am stuck trying to figure out how to modify the rolling average quickmeasure to include monthly averages in my table, below. When I created the quickmeasure, it is indeed averaging the total (see table total) BUT what I want it to do is include in each rolling average row the average per month (based of any months before each row. However, all it returns is the same values of the count they are averaging. Can someone help me figure this out? I included the DAX below.
So in my table the rolling average rows should include the average for may, then the average for may and june, then the average for may, june, and july, etc.
Help?
Solved! Go to Solution.
@rsderby68 , please, change the measure:
ID_rolling_avg =
VAR period = DATESBETWEEN ( 'Date'[Date], DATE ( 2022, 05, 15 ), MAX ( 'Date'[Date] ) )
RETURN
IF ( MIN ( 'Date'[Date] ) <= TODAY (), CALCULATE ( [ID_avg], period ) )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
In order to use time intelligence calculations mentioned in your example, you need a well-formed date table. I don't know the particularities of your data, but this is what you can try:
1. Create a Date table.
2. Create a Date column in your data table to use it for connection with the Date table.
3. Connect these 2 tables by Date column.
4. Create 2 measures:
ID_avg =
AVERAGEX (
ADDCOLUMNS (
SUMMARIZE ( Vimeo_Customers, 'Date'[Year], 'Date'[Month Name] ),
"@ids", CALCULATE ( COUNT ( Vimeo_Customers[Vimeo_Customer_ID] ) )
),
[@ids]
)
ID_rolling_avg =
VAR period =
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -1, YEAR )
RETURN
CALCULATE ( [ID_avg], period )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Sorry for all the appending messages but here is the full table. I think it is correct except I need to lock in a start date for the averages of may-2022 or 05/15/2022 if it can be that specific.
@rsderby68 , please, change the measure:
ID_rolling_avg =
VAR period = DATESBETWEEN ( 'Date'[Date], DATE ( 2022, 05, 15 ), MAX ( 'Date'[Date] ) )
RETURN
IF ( MIN ( 'Date'[Date] ) <= TODAY (), CALCULATE ( [ID_avg], period ) )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
I will also note that my date tables goes way into the future so I only want to include results where there is actual customer_id data which would only be the past (but I want to lock in start of 5/15/2022 launch) and up until 'today' date(today) whatever the current day is. If that makes sense?
Hi ERD,
I implemented the measures you listed and got this result below. I think the included dates on the rolling average is off because those numbers aren't correct averages. To specify, I want the average calculations to start in May-2022 (which is when we launched our new version) even thought there is data beforehand. And all other averages should build on that moving in the future from May-2022. Should I change something in the measure to lock that in? Note I do have a full date dates linked to my other tables (see below).
Thanks for your help! I think I am so close!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |