Combine 2 Values Depending on Week

DAX DAX Calculations
1

Hi All,

Struggling with getting a measure to work (I have not included the model as the client had me sign a NDA - Hopefully the model wont be needed but I guess i could build a simple model if required)

  1. In the source table there are 2 value columns - Budget and Actual by week number (not a calendar week format)

  2. Report includes a slicer where the week is selected.

  3. I need to show a graph where, depending on the week selected, it either gives the budget or Actual (anything greater than the week selected would equal budget and anything less than or equal to week selected would equal actual)

Could someone help please?

Thanks Forum…

2

Hi @Hitman,

If possible please share sample data.

  1. You want to see 2 separate lines for Actual and Budget or only one line ?
  2. What is in X axis ? Date or Week number ?

Try this

Create a measure if week number is less than or equal to selected week from slicer actual else Budget

Remove the filter interaction between Slicer and line chart

3

Hi Rajesh,

Apologies if i wasn’t clear.

  1. 1 line only (anything greater than the week selected would equal budget and anything less than or equal to week selected would equal actual)
  2. Week number

The suggested solution was exactly what i tried to do but was having a problem.

Assuming:

  1. the table and column name is Source[Week]
  2. and the selected week measure is [Selected Week]
  3. And the two values are [A] and [B]

I tried to do:

= If(Source[Week]> [Selected Week],[A],[B]

but it would not let me enter “Source[Week]” as i think this has to be a measure. I know im missing something simple but confused…

Thanks

4

Hi,

Apologies again. I got this working with the If function - User error my side

However the client has informed me that they want the data to show a solid line for the “actual” (which is up to the week selected only) and a dotted line for all weeks after the week selected only number and a dotted line for the budget

5

Hi @Hitman

That is also possible… Please share if you’ve sample data.

6

Will start a new topic tonight