DAX help for measure for that is used in a Line Chart (w/ Dates) and a Table (with no Dates)

[Anonymous]

Hi All,

I need some DAX help for a measure (call it SDA) that is an amount added to a measure that is used in both a Line Chart and a Table. Here is a portion of the SDA table in SQL:

DateKey MonthID Year Month Selling Days Monthly Selling Day Adjustment
20191101 201911 2019 NOV 22 0.04545500
20191201 201912 2019 DEC 21 -0.04761900
20200101 202001 2020 JAN 23 0.00000000
20200201 202002 2020 FEB 20 0.00000000
20200301 202003 2020 MAR 22 -0.04761900
20200401 202004 2020 APR 22 0.00000000
20200501 202005 2020 MAY 21 0.08695700
20200601 202006 2020 JUN 22 -0.10000000
20200701 202007 2020 JUL 23 0.00000000
20200801 202008 2020 AUG 21 0.04545500
20200901 202009 2020 SEP 22 -0.04761900
20201001 202010 2020 OCT 22 0.04347800
20201101 202011 2020 NOV 21 0.00000000
20201201 202012 2020 DEC 23 -0.04545500

So for a certain measure I want to add the SDA amount (i.e. the Monthly Selling Adjustment) to it. 

In the Chart it works fine.

Here is the measure before the SDA is applied    And here it is after 

If you look at Nov 2019 to Jan 2020 you can see the SDA applied to the bars

I also have a seperate measure in a table that does not have Dates.

Here is the partial DAX for the measure:

SWITCH (

SELECTEDVALUE ( SDAControl[IsSDA] ),

1,

(( ( [LM ACTUAL] - [LM PY] ) / [LM PY] )

+ [SDA]) * 100,

0, ( ( [LM ACTUAL] - [LM PY] ) / [LM PY] ) * 100

)

Essentially I have some logic that determines whether to apply the SDA to the measure

Before SDA is applied       After SDA is applied 

Since this is supposed to be for the "Latest Month" where data is loaded (i.e. the LM ACTUAL measure in the DAX) which happens to be Jan 2020 I would expect 0.0 to be added (just like in Jan 2020 in the Chart above). 

Any idea how I can apply a filter to this measure in the table to just add the latest month SDA and get it to work in the table?

[Greg_Deckler]

Well, you provided the sample data so that's great. If you could provide your measure formulas that would be awesome as we should be able to reconcstruct the issue and test some fixes. 

[Anonymous · Accepted solution]

Thanks Greg. I did manage to fix it. The logic is a bit complex but essentially I did this. I get my max date that has data populated in my fact table

VAR MAXDATE =

CALCULATE (

MAX ( 'Date'[Date] ),

FILTER (

ALL ( 'Date' ),

'Date'[Date] <= MAX ( 'Date'[Date] )

&& 'Fact_VOL'[LM ACTUAL] <> 0

)

)

Then I do my addition of this where I get the SDA of the month I care about (i.e. MAXDATE)

( ( [LM Actual] - [LM PY] ) / [LM PY] ) * 100

+ (

CALCULATE (

SUM ( SDA[Monthly Selling Day Adjustment] ),

FILTER (

'Date',

'Date'[Date] = DATE ( YEAR ( MAXDATE ), MONTH ( MAXDATE ), 1 )

)

)

)