Forum Discussion
DAX formula for Calculated column
- Anonymous4 years ago
Hi Kaustub
I think you didn't show me the logic of 16/5 in your sample. Here I build a sample to have a test.
My Data table is the same as your screenshot. I think you want to create a calculated column to calcualte daily output based on logic. 24/7 should be the average of month day, 24/6 shoule be the average of month day without Sunday and so on.
Firstly I create a date table by dax.
Date = ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ), "Day", DAY ( [Date] ), "DayName", FORMAT ( [Date], "DDDD" ), "YearMonth", YEAR ( [Date] ) * 100 + MONTH ( [Date] ) )I suggest you to create a YearMonth column in Data table first then create Planned Daily OutPut.
YEARMONTH = YEAR('Table'[Month&Year])*100+MONTH('Table'[Month&Year])Planned Daily OutPut = VAR _CountDay24_7 = CALCULATE ( COUNTROWS ( 'Date' ), FILTER ( 'Date', 'Date'[YearMonth] = EARLIER ( 'Table'[YEARMONTH] ) ) ) VAR _CountDay24_6 = CALCULATE ( COUNTROWS ( 'Date' ), FILTER ( 'Date', 'Date'[YearMonth] = EARLIER ( 'Table'[YEARMONTH] ) && 'Date'[DayName] <> "Sunday" ) ) VAR _CountDay24_5 = CALCULATE ( COUNTROWS ( 'Date' ), FILTER ( 'Date', 'Date'[YearMonth] = EARLIER ( 'Table'[YEARMONTH] ) && NOT ( 'Date'[DayName] IN { "Saturday", "Sunday" } ) ) ) VAR _CountDay24_4 = CALCULATE ( COUNTROWS ( 'Date' ), FILTER ( 'Date', 'Date'[YearMonth] = EARLIER ( 'Table'[YEARMONTH] ) && NOT ( 'Date'[DayName] IN { "Friday", "Saturday", "Sunday" } ) ) ) VAR _LogicCount = SWITCH ( 'Table'[Shift Configuration], "24/7", _CountDay24_7, "24/6", _CountDay24_6, "24/5", _CountDay24_5, "24/4", _CountDay24_4, BLANK () ) RETURN DIVIDE ( 'Table'[Monthly Planned OutPut], _LogicCount )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi amitchandak is it possible to use DAX formula to distribute the values over specific days based on the shift configuration? I am fairly new to Power BI and still learning.
Hi Kaustub
I think you didn't show me the logic of 16/5 in your sample. Here I build a sample to have a test.
My Data table is the same as your screenshot. I think you want to create a calculated column to calcualte daily output based on logic. 24/7 should be the average of month day, 24/6 shoule be the average of month day without Sunday and so on.
Firstly I create a date table by dax.
Date =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"DayName", FORMAT ( [Date], "DDDD" ),
"YearMonth",
YEAR ( [Date] ) * 100
+ MONTH ( [Date] )
)
I suggest you to create a YearMonth column in Data table first then create Planned Daily OutPut.
YEARMONTH =
YEAR('Table'[Month&Year])*100+MONTH('Table'[Month&Year])Planned Daily OutPut =
VAR _CountDay24_7 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER ( 'Date', 'Date'[YearMonth] = EARLIER ( 'Table'[YEARMONTH] ) )
)
VAR _CountDay24_6 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[YearMonth] = EARLIER ( 'Table'[YEARMONTH] )
&& 'Date'[DayName] <> "Sunday"
)
)
VAR _CountDay24_5 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[YearMonth] = EARLIER ( 'Table'[YEARMONTH] )
&& NOT ( 'Date'[DayName] IN { "Saturday", "Sunday" } )
)
)
VAR _CountDay24_4 =
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER (
'Date',
'Date'[YearMonth] = EARLIER ( 'Table'[YEARMONTH] )
&& NOT ( 'Date'[DayName] IN { "Friday", "Saturday", "Sunday" } )
)
)
VAR _LogicCount =
SWITCH (
'Table'[Shift Configuration],
"24/7", _CountDay24_7,
"24/6", _CountDay24_6,
"24/5", _CountDay24_5,
"24/4", _CountDay24_4,
BLANK ()
)
RETURN
DIVIDE ( 'Table'[Monthly Planned OutPut], _LogicCount )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.