Need help on DAX command which calculates based on day and week.

[iKitKat]

Hello, I created a bar-line graph which has three hierarchies: by week, by day, and by half hour. The graph represents "duration".

Here is an example of the data.

I need to write a DAX command which works out the use percentage depending on the time/day/week.

On the day level,

For values on a Wednesday, I divide the sum of the duration by 3. (e.g. 2 / 3 = 66.6% on the wednesday of the example)
For values on Monday, Tuesday, Thursday, and Friday, I divide the sum of the duration by 9. (e.g. 7/9 = 77.8% on the thursday).
If the sum is over 100%, the percentage is capped at 100% (e.g. 10/9 on the monday).

On the week level, 
The sum of duration is divided by 39/ (e.g. 33/39)
And if the sum is over 100%, the percentage is also capped at 100%.

Thank you for your time and help.

[Dangar332]

Hi, @iKitKat 

try below code

Measure = 
var a= WEEKDAY(MAX('Table'[date]),1)
var b = 
SWITCH(
    TRUE(),
   a=4,SUM('Table'[duration])/3,
    NOT(a in {4,6,7}),CALCULATE(
                         SUM('Table'[duration]),
                         FILTER('Table',NOT(WEEKDAY(MAX('Table'[date]),1) in {4,6,7})))/9
)
return
IF(b>1,1,b)

create new column of weeknumber

weeknu = WEEKNUM('Table'[date],2)

[iKitKat]

Thank you for your solution. Sorry, I should've mentioned that I wanted to incorporate a previous DAX measure I had help on. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-help-writing-a-DAX-command-whic...

[Anonymous]

Hi @iKitKat ,

Below is my table:

The following DAX might work for you:

Day = 
   var _dn = SELECTEDVALUE('Table'[Day Name])
   var result = 
   IF(_dn == "Wednesday",
    CALCULATE(
        SUM('Table'[Duration])/3,
        FILTER('Table','Table'[Day Name] == "Wednesday")
     ), 
    CALCULATE(
           SUM('Table'[Duration])/9,
           FILTER('Table','Table'[Day Name]<>"Wednesday" && (SUM('Table'[Duration])/9) <= 100)
   )
   )
   var result1 = IF(result <= 1,result , 1)
   return result1

Week = 
  var result = 
   CALCULATE(
      SUM('Table'[Duration])/39,
      FILTER('Table' , 'Table'[Column]<>0)
   )
  var result1 = IF(result <= 1,result , 1)
  return result1

Column = WEEKNUM('Table'[Date] , 2)

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[iKitKat]

Thank you for your reply and help. Sorry, I should've mentioned that I wanted to incorporate a previous DAX measure I had help on. https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-help-writing-a-DAX-command-whic...

[123abc · Accepted solution]

To achieve this in DAX, you can create a measure using the following logic. I'll provide you with an example DAX measure for both the day level and the week level based on the requirements you've mentioned:

For the day level:

Day Percentage =
VAR TotalDuration = SUM('YourTableName'[duration])
VAR DaysInWeek = 5

RETURN
IF (
WEEKDAY(MAX('YourTableName'[DateColumn])) = 4, -- Wednesday
MIN(1, TotalDuration / 3),
MIN(1, TotalDuration / DaysInWeek)
)

Replace 'YourTableName' with the actual name of your table and DateColumn with the column that contains the date.

For the week level:

Week Percentage =
VAR TotalDuration = SUM('YourTableName'[duration])
VAR WeeksInMonth = 4.33 -- assuming an average of 4.33 weeks in a month

RETURN
MIN(1, TotalDuration / WeeksInMonth)

Again, replace 'YourTableName' with your actual table name.

These measures use the SUM function to calculate the total duration for the selected period. The WEEKDAY function is used to identify if the date is a Wednesday. The MIN(1, ...) ensures that the percentage is capped at 100%.

Make sure to adjust the table and column names according to your actual data model. You can then use these measures in your bar-line graph to display the desired percentages.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[iKitKat]

Sorry everyone, I should've mentioned that I wanted this DAX to incorporate a previous DAX measure I had help on, namely https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Need-help-writing-a-DAX-command-whic...
@123abcI substituted var total duration with the previous DAX measure you help me on and your solution almost worked but when I select "show items with no data", the trendline disconnects if there is no data; I am not sure how to rectify this problem.