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

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 V
Super User V

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?

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!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors