cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Selected Dates Slicer DAX

Dear ,

 

I have below requirement,

 

  1. if value is 1 then result is 1
  2. if current row and previous row is 1 then result is 2.
  3. if current value is 0 then result is 0.

 

DateValueResult
9/11/202011
9/12/202000
9/13/202000
9/14/202011
9/15/202012
9/16/202000

 

I have written below DAX and it's giving current value when i am choosing all dates. However, when i am slicing only two dates (Example: 09/13, 09/15), still result is taking only previous day value.

 

DAX

Prev Day Values = SWITCH(

                    TRUE(),
                    SELECTEDVALUE(Table1[CNT])=1 && CALCULATE(SELECTEDVALUE(Table1[CNT]), PREVIOUSDAY('Date' [Date]))=1,SELECTEDVALUE(Table1[CNT]) + CALCULATE(SELECTEDVALUE(Table1[CNT]),PREVIOUSDAY ('Date'[Date])),
                    SELECTEDVALUE(Table1[CNT])=1, "1",
                    "0"  )

Please let me know how to make this DAX dynamic irrespective of date slicer.

 

Example If I choose 09/13 and 09/15, then DAX should take read 09/13 instead of 09/14 for 09/15 row.

2 REPLIES 2
Highlighted
Super User II
Super User II

Re: Selected Dates Slicer DAX

You cannot use PREVIOUSDAY(), you need to use MAX() or LASTNONBLANK  with the condition that they be smaller than the current value.  It's the standard "previous row" pattern.

 

Do you want help with the DAX for that?

Highlighted

Re: Selected Dates Slicer DAX

Yes please, i would appreciate it.

 

How to change my current DAX to handle my requirement with MAX()?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors