Need a way to add an adjustment to the value for a specific month that is dynamically selected

[Anonymous]

Hi All,

I need some help with values I have in a table called FactVolume which has a volume metric and keys relating back to respective dimension tables. One of those is a Date table which has Month (i.e. "Jan 2020", "Feb 2020", etc). On my report I have a textbox for an adjustment amount and a Date slicer showing months (this has no relationship currently to the other Date table). If a user puts in the value 10000 and selects "Apr 2020" I would like to add the value of 10000 to just the month of April and have the rest of the Volume values in FactVolume be the same (i.e. with no adjustment). If this were on a line chart the bottom axis would be the months and there would be a spike for the month of April. Again this adjustment is selected dynamically so I dont think i can use a Calculated column for this. Any help would be appreciated.

Scott

[mussaenda]

Hi @Anonymous ,

Kindly provide sample  data and expected result

[Anonymous]

My table is structured something like this (simplified a bit since I removed some of the other foreign keys - the DateKey is a "smart" key where 20191201 represents Dec 01 2019, 20200101 represents Jan 01 2020, 20200201 represents Feb 01 2020, etc):

DateKey ProjectVolume
20191201 34244781
20200101 30629411
20200201 31884789
20200301 30382500
20200401 28180872
20200501 31221904
20200601 32338541
etc

On my report I also have an adjusted textbox and a Date slicer where I can pick the month (this Date slicer is tied to an unrelated Date table to get its Month values):

I would like to produce a Line chart with Months on the bottom axis plotting the Projected volume with values from the table listed above like this:

For the month in the dropdown I would like to add the Adjusted Volume number to the projected volume for that month. So in this case for April 2020 I would like to see 28,180,872 + (-264,735) or  27,916,137. All of the other values for the other months I would like to remain the same. I have created a measure but I am getting the adjustment added to all months which is not what I want. I am fairly new to DAX so I would welcome any help here. Thanks

Scott

[Anonymous]

I am trying things with the measure I created like this:

Adjusted Project Volume =
IF (
    SELECTEDVALUE ( AdjustedDate[Month] ) = "Apr 2020",
    (
        SUMX (
            FILTER ( 'FactProjection', RELATED ( 'Date'[Month] ) = "Apr 2020" ),
            'FactProjection'[Total Project Volume]
        ) + Adjusted[Adjusted Value]
    ),
    'FactProjection'[Total Project Volume]
)

This gives me something like

April 2020 is now correct but as you can see the other months are not. For the other months they should be equal to the Total Project Volume measure. Any ideas on how I can achieve this??

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Adjusted Project Volume = 
SUMX (
    DISTINCT ( 'Calendar'[Month] ),
    IF (
        [Month] IN FILTERS ( 'AdjustedDate'[Month] ),
        SUM ( 'Table'[ProjectVolume] ) + [Adjustedt Value],
        SUM ( [ProjectVolume] )
    )
)

Best regards,

[Anonymous]

Perfect. Thank you so much!!