HELP! I need to calculate month-by-month and previous month based on values in a column.

[Anonymous]

Greetings, All,

I have a scenario that is about to drive me crazy! I need to find the utilization per month for presenters that work for my company across the country. As such, I need to accomplish a few different tasks:

• Count up the total number of “utilization days” (non-holiday work-days during which presenters gave events or were prepping for them);
• Find the total number of possible days during which presenters could have worked;
• Divide the utilization days by the possible days to find the utilization percentage.

This is an example of the default "function" that I want to make into a measure:

=(Remote + Event Prep + On-site)/Possible Utilization Days [meaning Non-holiday or weekend days]

I envision this would need to be a measure in Power BI using the CALCULATE and COUNTROWS DAX functions. I would also like to not only find the aforementioned values but also create a measure that does so for the month prior to a month selected using a slicer on a report page or, at the very least, create a measure that finds the values for the previous month with CALCULATE, COUNTROWS, and PREVIOUSMONTH or something like that.

Below are some screenshots. The first one shows a list of events along with their categories:

The second picture shows my Calendar table in which I mark each day's "day of the week number" as well as whether or not it was a holiday on our company calendar:

The third picture shows various functions I am using along with their results; the final result that I want to accomplish using DAX in Power BI is the last function:

I woud also like the DAX function to be flexible enough (if possible) to have it least each presenter's utilization during a given time period:

Any help y'all can offer would be incredibly appreciated!

[Seward12533]

Here are a few to get you started - trick is to let PowerBI do as much of the work for you with filter context.  If you add slicers to your page it will automatically filter the entire page.  you can modify specific visuals using Visual Filters, within a page using Page Fitlers and the entire report using report filters. If you could share a copy of your workbook with some representative (made up datea) it would help us help you. 

Woudl highly recommend spending some time to learn the basics of PowerBI it a completly different paradigm and you will drive  yourself crazy if you try to appply the Excel paradigm.  Also you may even find it easier to work in PowerPivot in Excel (excatly the same as PowerBI as far as data model but the interface is a little different.  I find new users find it easier as they can quickly check thier model and results in Excel using techniques they are familar with. Odds are you have it in your excel and you just need to turn it on. (tip its a com add in you need to activate in anyting after Excel 2013

Also be sure to include a Date Table in almost all your models. once you link the date you just need to drag in week, month year or whatever. do a search on DAX CREATE Date Table you should be able to find some code to reuse quite easily. 

Here is some dax to get you started. 

First be careful of Averages of Averages etc.. I usually convert PCT to specific values say hours my multiplying out if you dont  have it already in the underlying data. 

Measures to calculate specific totals for the categories
Utilization Pct = SUM(fact[Utilization])

Possible Days = CALCULATE(COUNTROWS(date[Date]),date[HolidayQ]="N") // same as COUNTIF
default function = [Utilization Pct]/[Possible Days]
default function Previous Month = CALCULATE([default function],PREVIOUSMONTH(date[Dates])

If you don't need a subset of the categories the above should work and PowerBI will do its magic and calculate each value in context of where its calculated.  

If you need specific measures for each of these then 
Remote Utilization = CALCULATE([Utilization Pct],fact[Category]="Remote") // similar to SUMIF, AVGIF its really anyting IF
Event Prep Utilization = CALCULATE([Utilization Pct],fact[Category]="EventPrep")
...

[Anonymous]

Seward12533,

Thank you for your suggestions! Here is a link to the sample data used in this demonstration.

Oh, and just to clarify, the Utilization column should not have been included as the percentages shown there should not be summed due to the fact that they are repeating because they correspond to each individual's utilization as a whole.

[Seward12533]

Here you go! (GO MARK!! he is a beast!)

https://1drv.ms/u/s!AuCIkLeqFmlhhJkOw0AxghYLnzFEsw

Key Measures

Day Count = COUNTROWS(Scheduling)
Number Working Days = CALCULATE(COUNTROWS(DateDim),FILTER(DateDim,DateDim[Holiday?]="N"&&DateDim[Day of Week Monday Start]<=5))
Utilization = DIVIDE( [Worked Days],[Number Working Days])

[Anonymous]

Seward,

That is perfect! One follow-up: I noticed that I am unable to add categories to a slicer and slice the visuals by category. Is there any way to make this work? I found this strange because I can click the column headers for the matrix in the top-left corner of your example and have the visuals and cards filtered accordingly.

Thank you again!

[v-piga-msft]

Hi @Anonymous,

Do you mean you cannot add the Category to Slicer and slice the other visuals like below?

If it is, please share some screenshots about your slicer and which category column do you use?

Best  Regards,

Cherry

[Anonymous]

Cherry,

Thanks for pointing that out! I didn't even think about using a field in a different table.

However, the issue still persists when I add Scheduling[Catgory] to a slicer as it has no effect on the column visual. I am just trying to understand why that field has no effect on related data but Categories[Category] does. Is there some setting behind the scenes?

Thank you again!

[v-piga-msft]

Hi @Anonymous,

Sorry for my misunderstanding.

---

However, the issue still persists when I add Scheduling[Catgory] to a slicer as it has no effect on the column visual.

For your requirement, this issue should be caused by the relationship between the tables.

As your data model, you could find that you don't create the relationship between the table Scheduling and table _Measures 1, so when you create the Scheduling[Catgory] as a slicer, it won't filter the measures in table _Measures. 

In addition, you could see that there is a relationshiop between table Scheduling and table Categories, so Categories[Category] could filter the measures in table _Measures 1.

Best Regards,

Cherry