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.
I am looking to alter three DAX formula so that the red boxes are not calcuated (blank) and the red-stars are calcuated as 90 and 90
Hours spent. = SUM(Table46[Hrs])
Number of preceding periods. = COUNTROWS(FILTER(CALCULATETABLE(SUMMARIZE('Calendar',[Year-month],"ABCD",[Hours spent.]),DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),min('Calendar'[Date])-1)),[ABCD]>0))
3 months rolling average. = if([Number of preceding periods.]=2,AVERAGEX(DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),MAX('Calendar'[Date])),[Hours spent.]),BLANK())
Solved! Go to Solution.
Hi,
Hope this works. Download the file from here.
Hi,
Share the link from where i can download your PBI file.
Thank you Ashish - I can't get the 3m average into the rows. They way you orignally had this looked really nice.
https://www.dropbox.com/sh/93zvi0qoytr1owv/AABDXX_CpkIB_EpRhLqiIBjfa?dl=0
Hi,
Is this your expected result? You may download my file from here. Here are the revised measures:
Number of preceding periods.. = COUNTROWS(FILTER(CALCULATETABLE(SUMMARIZE('Calendar',[Year-month],"ABCD",[Hours spent..]),DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),min('Calendar'[Date])-1)),[ABCD]>=0))
3 months rolling average.. = if(ISBLANK([Hours spent..]),BLANK(),if([Number of preceding periods..]=2,AVERAGEX(DATESBETWEEN('Calendar'[Date],EDATE(MIN('Calendar'[Date]),-2),MAX('Calendar'[Date])),[Hours spent..]),BLANK()))
Select the visual, click to the Format button in the visualisation pane and swith the "Show on rows" button to On.
Hope this helps.
Thank you for sorting out the red boxes.
The new formula works great with zeros in the database. But not for when there are blanks. For example the 109 below should consider the blank (114 + 0 + 104) / 3 = 73
Can we have the first two months not populate averages like the old formula?
This is very education for me and thank you again for your help - if I can wire you a coffee via Paypal PM me 🙂
Hi,
Hope this works. Download the file from here.
Hi Ashish - Thank you so much. Thats perfect. Loving the new months elapsed expression! Having it on the calendar table makes it all the more robust and reuseable
You are welcome.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |