Forum Discussion

danielpaduck's avatar
danielpaduck
Helper III
2 years ago

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

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!

 

 

 

Need Help

6 Replies

  • gadielsolis's avatar
    gadielsolis
    Resolver III
    2 years ago

    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's avatar
      danielpaduck
      Helper III
      2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      danielpaduck
      Helper III
      2 years ago

       

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.