Forum Discussion
nj-matt
Helper I
Helper IHelp with PREVIOUSDAY and weekends
I have a dataset that is updated each workday - trying to use the PREVIOUSDAY dax formuls that works like a charm, but I have an issue with non consecutive days. Example - Date Day of We...
Hi nj-matt ,
You can create a measure as below:-
_Measure = VAR _current_date = MAX ( 'Table (3)'[Date] ) VAR _prev_date = CALCULATE ( LASTDATE ( 'Table (3)'[Date] ), FILTER ( ALL ( 'Table (3)' ), 'Table (3)'[Date] < _current_date ) ) RETURN CALCULATE ( MAX ( 'Table (3)'[Sales] ), FILTER ( ALL ( 'Table (3)' ), 'Table (3)'[Date] = _prev_date ) )
Samarth_18
Community Champion
Community ChampionHi nj-matt ,
You can create a measure as below:-
_Measure =
VAR _current_date =
MAX ( 'Table (3)'[Date] )
VAR _prev_date =
CALCULATE (
LASTDATE ( 'Table (3)'[Date] ),
FILTER ( ALL ( 'Table (3)' ), 'Table (3)'[Date] < _current_date )
)
RETURN
CALCULATE (
MAX ( 'Table (3)'[Sales] ),
FILTER ( ALL ( 'Table (3)' ), 'Table (3)'[Date] = _prev_date )
)
nj-mattHelper I
Helper II spoke too soon - this measure does work but it seems to only work for the entirety of the data on the prior date.
So same example above, if I check Dec 19 vs Dec 16 correctly but any grain other than total returns repeated values.
I first changed the formula from ALL to ALLSELECTED for the RETURN porition which worked but again, only returning the total.
SO for exampe if I then pull in quarters I see this -->
| Date | Sales | Sales Prior |
| 12/19 Q1 | 20 | 75 |
| 12/19 Q2 | 30 | 75 |
| 12/19 Q3 | 30 | 75 |
| 12/19 Q4 | 20 | 75 |
| 12/19 Total | 100 | 75 |