How to write an IF measure when using different time periods

[SonyT01]

Hi,

Please can someone show me how to write the following IF measure. 

I have an example where I have created a table in Power BI that looks similar to the below.  I have calculated a 'YTD' field and a 'YTD' Mar 20 field.  The table has a date slicer that is selecting August 2020.

I would like to create the 3rd column in the table to look like 'YTD New'.  However I can't seem to work this out.  The logic of the measure I want is:

YTD New = If Name = "Cash" then 'YTD Mar 20' otherwise 'YTD' 

Name	YTD	YTD Mar 20	YTD New
Receipts from customers	400	350	400
Payments to suppliers	300	250	300
Interest paid	50	25	50
Cash	100	40	40

The other 2 columns are calculated by:

Measure:

YTD Mar 2020 = CALCULATE(SUM('Table1'[Balance]),DATESBETWEEN(

Dates[Date],

DATE(2020,3,1),

DATE(2020,3,31)

)

)

Calculated column:

YTD = ('Table1'[YTD Debit] - 'Table1'[YTD Credit])

I do hope this is achievable somehow.

[DataInsights · Accepted solution]

@SonyT01,

Try this measure:

YTD New = IF ( MAX ( Table1[Name] ) = "Cash", [YTD Mar 2020], MAX ( Table1[YTD] ) )

[v-yuaj-msft · Accepted solution]

Hi @SonyT01 ,

According to your description and my previous experience, I guess you seem to mix measures and calculated columns. It does not meet the definition rules of DAX functions.

This error can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Result:

I think you can modify the "YTD NEW" measure appropriately as follows.

YTD NEW =

                  var min_value=MIN('Table 1'[Name])

                  return

                 IF(

                        min_value="cash",

                        'Table 1'[YTD Mar 2020],

                       MIN('Table 1'[YTD])

                     )

Result:

I hope my suggestion can give you some help.

Best Regards,

Yuna

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

[v-yuaj-msft · Accepted solution]

Hi @SonyT01 ,

According to your description and my previous experience, I guess you seem to mix measures and calculated columns. It does not meet the definition rules of DAX functions.

This error can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Result:

I think you can modify the "YTD NEW" measure appropriately as follows.

YTD NEW =

                  var min_value=MIN('Table 1'[Name])

                  return

                 IF(

                        min_value="cash",

                        'Table 1'[YTD Mar 2020],

                       MIN('Table 1'[YTD])

                     )

Result:

I hope my suggestion can give you some help.

Best Regards,

Yuna

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

[SonyT01 · Accepted solution]

Thank you so much!!  I have been trying to figure this out with no success.  This has achieved the result I was after.

Many thanks again! 

[DataInsights · Accepted solution]

@SonyT01,

Try this measure:

YTD New = IF ( MAX ( Table1[Name] ) = "Cash", [YTD Mar 2020], MAX ( Table1[YTD] ) )

[MV123]

Thank you for your help. It did work well for each category, but not for the total amount.

I was trying to calculate a % of accomplishment for each category, but the Total % achievement it just sums each category, it doesn't calculate the correct amount.

Do you know how can i fix it?