Calculate Question - How To Keep A Column Value In The Calculation

[danielpaduck]

Hi,

How is it possible to keep/evaulate the column 'Period' 

In my Calculate function?

Labor Hours Conditional (D) =

var minPeriod = CFGDates_Min_Max[Min Period (msr)]

var maxPeriod = CFGDates_Min_Max[Max Period (msr)]

Return

CALCULATE('LD Actual'[Hours (Actual)],

'LD Actual'[Period] = minPeriod) +

CALCULATE('LD Forecast'[Hours (Forecast)],

'LD Actual'[Period] > minPeriod)

Basically, I am just trying to say 'If period is 201901 then return the actual posted labor hours. If the period is greater than 201901 then return forecasted hours.   Using this calculation returns the sum and disregards the period column itself. 

Thanks!

[Ashish_Mathur]

Hi,

The approach should be as follows:

1. Create a Calendar Table with calculated column formula for Year, Month name and Month number.  Sort the Month name column by the Month number column
2. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
3. To yoru visual, drag Year and Month name from the Calendar Table
4. Write this measure

Measure = if(month(min(calendar[date]))=1,[Actual],[Forecast])

Hope this helps.

[danielpaduck]

I will try ths. 

[v-binbinyu-msft]

Hi @danielpaduck ,

Please try below dax formula:

Labor Hours Conditional (D) = 
VAR minPeriod = MIN(CFGDates_Min_Max[Min Period (msr)])
VAR maxPeriod = MAX(CFGDates_Min_Max[Max Period (msr)])
RETURN
SUMX(
    'LD Actual',
    IF(
        'LD Actual'[Period] = minPeriod,
        'LD Actual'[Hours (Actual)],
        IF(
            'LD Actual'[Period] > minPeriod && 'LD Actual'[Period] <= maxPeriod,
            CALCULATE(
                SUM('LD Forecast'[Hours (Forecast)]),
                'LD Forecast'[Period] = 'LD Actual'[Period]
            ),
            BLANK()
        )
    )
)

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danielpaduck]

The DAX was complaining about the Period from the LD Actual table. 

[gadielsolis]

Could you please share the PBI file? Or create a new one with random data and share in here in order to have a better undestanding of the issue you're facing.

[danielpaduck]

I am not sure how to attach the workbook. I only see links.  Thanks!