Regular Visitor

5 years ago

Solved

DAX formula for Calculated column

Hello Power BI community,

I am stuck with an issue & need the PBI community’s help.

I have the below columns.

First column tells the “Month & Year”

Second column is for the Production “Tool ID”

The 3rd column is for the “Shift Configuration” for the production run. Basically, this tells that based on the demand if the tool is going to run 24/7, 24/5 etc.

The monthly Planned output is the, planned production output for the respective “tool” for each month.

I will be merging a date table which would have the dates for each month, the day(Sun, Mon, Tue, Wed etc.) and the number of days in a 4-4-5 Month.

If someone can help me with a DAX formula to get a calculated column (highlighted in yellow) based on the following points

If the shift configuration is 24/7 then the monthly output is distributed equally over the number of days in a month
If the Shift configuration is 24/6 then the monthly output is distributed equally over the number of days in a month BUT “0” for Sundays
If the Shift configuration is 24/5 then the monthly output is distributed equally over the number of days in a month BUT “0” for Sundays & Saturdays
If the Shift configuration is 24/3 then the monthly output is distributed equally over the number of days in a month BUT “0” for Sundays, Saturdays & Fridays

Anonymous

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.

Daily output.pbix31 KB

3 Replies

amitchandak
Super User
5 years ago
Kaustub , for equal distribution you can refer to my file(attached) of my blog, You can add flag using weekday for table

Weekday([Date],1)

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

value Split between months start end date.pbix167 KB

Kaustub

Regular Visitor

5 years ago

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.

Anonymous

Not applicable

5 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.

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.

Daily output.pbix31 KB

Forum Discussion

DAX formula for Calculated column

3 Replies

Related content

TMDL - calculated column

If formula

DAX Formula Help

Vary the calculated column formula

formula