Average Daily MTD and avg daily MTD last year for all working days

[Reine]

Hi all - I'm stuck at my limited DAX knowledge and desperately need some help!

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.
sample date table

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:

Thanks in advance for the help - I'm pulling my hair out over here 😞 

[Anonymous]

hi @Reine : what a sticky problem! Assuming a relationship exists between your data table and your date table, just change the following part of the formula.

OLD: VAR cntDates = COUNTROWS(DISTINCT(AppointmentsT[Date]))

NEW: VAR cntDates = SUM(DateTable[IsWorkingDay])

The reason things are failing for you is that you are only counting the dates in your appointments table, not in your date table.

[Reine]

@Anonymous 
I feel like it has something to do with the 'ALL" part of the formula in the other measures but I don't quite understand what that is doing so don't know how to change it.  

[Anonymous]

Yes, the ALL formula would cause the denominator to be far too large, which is what we are seeing. The problem here is that DAX calculates & scopes relative to your data model, so the solution you used is for a different data model, hence the issues you are seeing. So unfortunately, its not as simple as "what formulas do I fix", its a two-step problem of "what does my data model need to do in order to meet my goal" and then "which measures do I need to use on this model".

I will take a look at this when I have some more time (later today), and mockup a data model + measures that will allow you to get the correct data in the output table.

[Reine]

@Anonymous  thank you and no worries - I've been trying to do this for 2 weeks so another couple days won't matter 🙂  I maybe don't really understand what is meant by a data model.  I connect to our practice management software and it has an appointments table that I use as is

[Anonymous]

@Reine: Ok so after stepping back and thinking about this, I know why my proposed solution didn't work. You aren't using filters on the data in PowerBI directly, you are enforcing filters inside of DAX. Which is fine, I guess? So that means that if I suggest a query on the date table, which is unconstrainted, you get too large of a denominator.

Anyway, I mocked up the data and simplified this so my simple brain can understand it.

https://1drv.ms/u/s!AhFUa5zn5xJwwC5si9mH3b-QPLZw?e=gS34dM

I created a new model, with the fact, date, and dimension table for location. You will notice that I am only exposing the dim table and the measures, we want to hide all the other tables so we don't use them accidently.

ok so I created a host of measures - the good news is that if you are familiar with excel at all you will immediately understand them.

and as a last note, I added some new fields to the date calendar table. A simple IF statement to each date to check if it is the current month, the same month last year, and last year. That makes our COUNT transactions super simple!

Example of the new DAX:

CM_Appt = CALCULATE(COUNT(fact_Appt[ApptID]),'date'[CurrentMonth]="Yes")

Don't need distinct, don't need ALL(), don't need FILTER.

Take a look and let me know your thoughts.

[Reine]

@Anonymous  thank you for taking the time to do this.  It's very helpful to see another way to go about this. Something still isn't quite right though.  It looks to me like the calculations are for the entire current month (this year and last) rather than current MTD - is that right?  What I need is for it to always show current month to date - and it will change automatically with each additional day in the month and start over when the next month starts.  Does that make sense?

[Anonymous]

@Reine: Right you are.

To fix that, we just need two formula changes. I've saved it in the one drive file linked previously as well if you want to see it.

PriorYearCurrentMonth =
IF (
    AND (
        AND (
            YEAR ( TODAY () ) - 1
                = YEAR ( 'date'[date] ),
            MONTH ( TODAY () ) = MONTH ( 'date'[date] )
        ),
        DAY ( 'date'[date] ) <= DAY ( TODAY () )
    ),
    "Yes",
    "No"
)

CurrentMonth =
IF (
    AND (
        EOMONTH ( TODAY (), 0 ) = EOMONTH ( 'date'[date], 0 ),
        'date'[date] <= TODAY ()
    ),
    "Yes",
    "No"
)

[Reine]

@Anonymous  Hi - the file doesn't seem to be updated?  It looks the same to me - sorry if I am just missing it.  (Though I do see bear vid!  looks like PNC to me)

I'm unclear what measures I should replace with these. Will these two changes kick back all days MTD or just my working days?

[Anonymous · Accepted solution]

@Reine: PNW indeed 🙂

Sorry for confusion - there is a lot of nuance to how to solve your problem and its hard to capture all of that in writing. So I will try again. The file is updated - link shared again for context - https://1drv.ms/u/s!AhFUa5zn5xJwwC5si9mH3b-QPLZw?e=dgeHZy

What I did first is mockup your data in excel, and then import it into PowerBI. Then I did some light transforms in power query to adapt the model to a "fact-dim-measure" data model. You can see these by looking at the "Transforms" view in PowerBI.

What is non-obvious, though, is that I added 3 calculated columns to the date table in PowerBI directly. Those columns are  where the formulas lie to determine if a date matches current month to date, prior year month to date, and prior year.

Then, once I had my data prepped, I began to add measures.

For appointments, we are taking the count of apptID in a certain range. This pattern repeats for all appt related measures.

For workdays, we are taking the sum of "WorkDay", which is a calculated field returning a 1 or a 0 based on if a day is a weekend or not (I recognize you are doing it differently with holidays, but I'm ignoring that nuance). This pattern again repeats for all work day related fields.

So to answer the question, will these changes kick back to all days? No, because all measures reference Sum of workdays, not total days. And should you change measures? No, you should change calculated columns on the date table as indicated above.

Hope you find this helpful - you are so close to solving this and at this point I feel very committed to helping close it out!

[Reine]

@Anonymous  so sorry - I had measures on the brain and thought thats what the formulas were for.  Yes, I had noticed the additions to the date table and made them originally.  I have now updated those two and it works perfectly!  It seems to me now that this will update automatically every day and/or as my appointment counts change and I won't have to do a thing - yay!

I have just realized something that throws a wrench in things though. 😞  One of the locations actually has different working days than all the others.  That location is closed on Fridays in addition to the weekend and holidays. I'm so sorry - I was so focused on the DAX that I didn't even think about that one location having different working days.  Ugh.  Would I just create another column in my date table for "IsWorkingDayLocation4"?  And then somehow use that for just the one company? I still need my visual to include all locations like it does.

I cannot tell you how much I appreciate your help with this.  I have spent so many hours trying to figure this out.   What I really need to do is some actual training. I have the basics down but I need to understand DAX and modeling better.  Do you have any experience with Enterprise DNA training courses? 

[Anonymous]

I recommend the Chandoo.org training, can't speak for other courses.

the solution for your location problem requires something called a bridge table (or more complex DAX). I would rather keep it simple, so I will build the bridge and share with you.

[Anonymous]

@Reine: Ok one more time.

File is updated again - https://1drv.ms/u/s!AhFUa5zn5xJwwC5si9mH3b-QPLZw?e=43Y4lZ

I added a bridge table, that looks like this:

Then I added a "JoinKey" to the bridge table and the fact table since you can't create relationships on multiple columns in powerBI. That is the far right column of the table above. This facilitates the relationship of these tables below:

As a last step, all measure referencing 'date'[WorkDay] were updated to reference 'bridge_DateLocation'[WorkDay].

Example: CM_WorkDays = CALCULATE(SUM(bridge_DateLocation[WorkDay]),'date'[CurrentMonth]="Yes")

I want to emphasize this - those that are better in DAX than I am could write a single DAX expression to solve this. I'm not as good as those folks so I have to rely on materializing interim data in my data models. I find this infinitely easier to debug though.

[Reine]

@Anonymous  yikes.  This is even harder for me to wrap my head around than DAX!   I think it will take a bit to recreate this in my pbix as I need to look up how to make a seperate table like you've done and some things are a little different (I don't have a seperate locations table) but I think I can work through how to do it.  Thank you so much for all of your help.  After weeks trying to figure out the DAX, I feel very relieved to have a working solution.  You are a life saver!!

[Anonymous]

Take a look at the code in the "Transforms" section of PowerBI and you can copy that to make a location table.

The workflow is reference any table that has all location in it as a column, remove all other columns exception location (and your other location attributes, i.e. name or description), and then remove duplicate rows. The code is below for how I created a location table.

let
Source = fact_Appt,
#"Removed Other Columns" = Table.SelectColumns(Source,{"LocationID"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"LocationID", Order.Ascending}}),
in
#"Sorted Rows"

[Reine]

@Anonymous  The "transforms" (query editor right?) is just all errors because it doesn't actually connect to data I think. 

I have to admit that I did not create a seperate "location" table because I didn't think I needed to since it's in my data table already and everything seems to work without creating a separate table.  Is there a reason behind doing that that I am not understanding?  

[Anonymous]

Building a "fact-dim-measure" pattern data model will make it more understandable to data users. You don't have to do it.

More on the "star schema" can be found here - https://docs.microsoft.com/en-us/power-bi/guidance/star-schema

[Reine]

@Anonymous  thank you - I will read up on the star schema.  A fact/dim/measure model does make total sense to me.  I just had never really thought of it in that way.  I will check out Chandoo as well.   I have been using power bi with an Excel mindset and just what I can do with the sort of dragging and dropping easy features of Power Bi.  Now that I am trying to take things to the next level I realize I need to back up and get a better understanding of modeling, DAX, etc.  I very much appreciate all of the time you have spent helping me - both giving me the answers as well as explaining in a way that actually teaches me some things!  

[Reine]

@Anonymous  oops typo - I meant PNW 🙂 

[Reine]

Thank you for responding @Anonymous .  Unfortunately this is not working 😞   I changed that line in my Avg Daily Measure and this is what the visual looks like now:
visual with change

I do have a relationship between data and date table: