Need help with Average Daily MTD and avg daily MTD last year

Anything related to PowerPivot and DAX Formuale
Reine
Posts: 1
Joined: Tue Jun 02, 2020 3:59 am

Need help with Average Daily MTD and avg daily MTD last year

Postby Reine » Tue Jun 02, 2020 4:26 am

Hello - I am in desperate need of help with DAX. I'm new to Power BI and DAX and up to now have been doing some reports that mainly use Power BI's click and drag tools. I am now asked for a more complicated report and having a difficult time.

I am trying to create a dynamic visual that shows our average daily appointments (by location) MTD and MTD last year to help us see how the COVID 19 restrictions are affecting us. I have a data table that has all appointments along with appt date, appt ID, and Location and I also have a date table. I added a "isworkingday" column to my date table that also takes into account holidays and that is working. My date table is
[attachment=2]sample appt data.PNG[/attachment] [attachment=1]sample date table.PNG[/attachment]

I got some DAX elsewhere that works perfectly and I thought I was done, but then realized it calculates on only days that actually have appointments and I need it to calculate on all workings days instead. After hours of trying various things, I just cannot work out what to update to make this work. My DAX knowledge is still limited so there are parts of the below formulas that I just don't understand so I don't know how to change it properly.

Here is the current DAX I am using:


Avg Daily =
VAR cntAppointments = COUNTROWS(DISTINCT(AppointmentsT[ID]))
VAR cntDates = COUNTROWS(DISTINCT(AppointmentsT[Date]))
RETURN
DIVIDE(cntAppointments,cntDates)

AVG MTD =
VAR StartDate = DATE(YEAR(TODAY()),MONTH(TODAY()),1)
VAR EndDate = TODAY()
Return
CALCULATE( [Avg Daily],ALL('Date'[Date]),
AND ( AppointmentsT[Date] >= StartDate, AppointmentsT[Date] <= EndDate))


Avg Daily Appts Current Month 2019 =
VAR StartDate = DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)
VAR EndDate = EOMONTH(StartDate,0)
Return
CALCULATE( [Avg Daily],
ALL('Date'[Date]),
AND ( AppointmentsT[Date] >= StartDate, AppointmentsT[Date] <= EndDate))


% avg daily appts we are down this year vs last = [AVG MTD]/[Avg Daily Appts Current Month 2019]*1

AVG daily appts all 2019 =
VAR StartDate = DATE(2019,1,1)
VAR EndDate = DATE(2019,12,31)
Return
CALCULATE( [Avg Daily], ALL('Date'[Date]),
AND ( AppointmentsT[Date] >= StartDate, AppointmentsT[Date] <= EndDate))

This is the current visual using all the DAX above that calulates on only days that had appointments. This updates automatically as appointments are scheduled or cancelled:
[attachment=0]working visual only appt days.PNG[/attachment]

Thank you in advance for your help. I have purchased several books including Supercharge Power BI and intend do do some learning but I am not there yet and I need to get this report working!
Attachments
working visual only appt days.PNG
current visual that works but calculate only on days that have appointments
working visual only appt days.PNG (13.21 KiB) Viewed 575 times
sample date table.PNG
Sample of date table - there are more columns than these
sample date table.PNG (42.68 KiB) Viewed 575 times
sample appt data.PNG
Sample of appointment table - there are more columns than these but showing what is relevent
sample appt data.PNG (26.05 KiB) Viewed 575 times

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron