I have a charges table that lists Practice Name, Location Name, Charge Amount, System Month, System Year. The date range is from 1/1/2021 to current date.
Example: I need to calculate Average Daily Charge Amount for the previous 3 months. So if I am looking at todays date 5/26/2023 I want to calculate total Charges for Feb, Mar, April 2023 and divide that by Days in that period.
This is in PowerBI. I have tried so many different options and can’t get it to work.
You would need to use an iterator like AVERAGEX, the table you would iterate over would be the date table, as that would be at the day granularity, the filters placed on it would be the dates between then current date and the date 3 months back.
In a measure you would need to selected date so set a VAR MAX(Date[Date]), create another VAR lastdateinrange which will be the selected rolled back he correct period, you can use DATEADD for standard periods, create another VAR datestouse which will be DATEBETWEEN using the selected date and the last date, this will be the table used to filter, then you would be able to use CALCULATE which AVERAGEX as the measure, and the dates to use variable as the filter modifier.
Hello @carlinlentz
Did the responses above help solve your query?
If not, can you let us know where you’re stuck and what additional assistance you need?
If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION
Thank you
Hi @carlinlentz
We’ve noticed that no response was received from you on the post above.
Just following up if you still require assistance to solve your inquiry.
In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @carlinlentz
Due to inactivity, we’d like to conclude that you no longer require assistance to solve your inquiry.
If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.