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.
Hi,
I'm currently working on a report to bring together all of our main datasets. This includes a page per dataset and then one to combine relevant data. I've created a calendar table to link the date element of all datasets together. The issue i'm having is with the moving average measure for one dataset, this is because there is a year's less data. All other datasets start on 04/01/2015 while this one only goes back to 04/01/2016. This is causing the April 2016 moving average to include previous months as if the value is 0.
The DAX i'm using for moving average is:
Confirm Moving Average = IF(COUNTROWS(VALUES('Calendar'[MonthNumber]))=1,CALCULATE([Count of Confirm Records]/COUNTROWS(VALUES('Calendar'[MonthNumber])),DATESBETWEEN('Calendar'[Date],FIRSTDATE(PARALLELPERIOD('Calendar'[Date],-2,MONTH)),LASTDATE(PARALLELPERIOD('Calendar'[Date],0,MONTH))),ALL('Calendar')))
My question is, is there anything I can add into the DAX so that it only includes data from 04/01/2016 rather than the whole calendar dates column.
I'm relatively new to Power BI so any advice would be greatly appreciated.
Solved! Go to Solution.
What if you add a filter to the DAX? For further questions, please post some sample data(in plain text or file, we can't copy from a snapshot) and expected output.
Confirm Moving Average = IF ( COUNTROWS ( VALUES ( 'Calendar'[MonthNumber] ) ) = 1, CALCULATE ( [Count of Confirm Records] / COUNTROWS ( VALUES ( 'Calendar'[MonthNumber] ) ), DATESBETWEEN ( 'Calendar'[Date], FIRSTDATE ( PARALLELPERIOD ( 'Calendar'[Date], -2, MONTH ) ), LASTDATE ( PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ) ) ), FILTER ( 'Calendar', 'Calendar'[Date] >= DATE( 2016, 4, 1 ) ) ) )
What if you add a filter to the DAX? For further questions, please post some sample data(in plain text or file, we can't copy from a snapshot) and expected output.
Confirm Moving Average = IF ( COUNTROWS ( VALUES ( 'Calendar'[MonthNumber] ) ) = 1, CALCULATE ( [Count of Confirm Records] / COUNTROWS ( VALUES ( 'Calendar'[MonthNumber] ) ), DATESBETWEEN ( 'Calendar'[Date], FIRSTDATE ( PARALLELPERIOD ( 'Calendar'[Date], -2, MONTH ) ), LASTDATE ( PARALLELPERIOD ( 'Calendar'[Date], 0, MONTH ) ) ), FILTER ( 'Calendar', 'Calendar'[Date] >= DATE( 2016, 4, 1 ) ) ) )
Thank you, this works perfectly. I will do so in future posts, thanks for the advice.
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |