Forum Discussion

Kaustub's avatar
Kaustub
Regular Visitor
4 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...
  • Anonymous's avatar
    Anonymous
    4 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 Zhou

     

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