Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kevderbeste
Helper III
Helper III

SHOW 10 DAYS BACK TO SELECTEDVALUE

hi, i tried with a column with this sintax

 

column = if( table[date] >= max(table[date]) -10 , sales)

 

with this column i can show static data, i need to replace the max(table[date]) to selectedvalue(table[date]) in order to can show dinamic data in my chart. 

 

thanks in advanced.

 

leave my dashboard with data here https://1drv.ms/u/s!AtEkAF7ffIsqg8RWv84QIBUlFwYLkg?e=hE4NTt

1 ACCEPTED SOLUTION

Hi  @kevderbeste ,

 

I created a sample table as below:

Annotation 2020-05-07 105825.png

First create a slicer table using below dax expression:

 

 

Slicer = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

 

 

Then create a measure as below:

 

 

Measure = IF(SELECTEDVALUE('Slicer'[Date])=BLANK(),SUM('Table'[Sales]),IF(MAX('Table'[Date])>=SELECTEDVALUE('Slicer'[Date])-10&&MAX('Table'[Date])<=SELECTEDVALUE('Slicer'[Date]),SUM('Table'[Sales]),BLANK()))

 

 

Finally you will see:(If no date is selected)

Annotation 2020-05-07 110743.png

if one date is selected:

Annotation 2020-05-07 110931.png

 

 

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@kevderbeste , not very clear

You can get 10 days back sales like

10 Days behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(Table[Date],-10,Day))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

hi @amitchandak  thanks for the information, is not the solution for my request, cause i need to show the dates in x axis, and with this mesaure i have an acumulated from data, and went i put this measure in line chart 1. show all dates and 2. if i selected a date just show one date, and i need to see 10 days back to selected. 

 

thanks! 

Hi  @kevderbeste ,

 

I created a sample table as below:

Annotation 2020-05-07 105825.png

First create a slicer table using below dax expression:

 

 

Slicer = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

 

 

Then create a measure as below:

 

 

Measure = IF(SELECTEDVALUE('Slicer'[Date])=BLANK(),SUM('Table'[Sales]),IF(MAX('Table'[Date])>=SELECTEDVALUE('Slicer'[Date])-10&&MAX('Table'[Date])<=SELECTEDVALUE('Slicer'[Date]),SUM('Table'[Sales]),BLANK()))

 

 

Finally you will see:(If no date is selected)

Annotation 2020-05-07 110743.png

if one date is selected:

Annotation 2020-05-07 110931.png

 

 

 

For the related .pbix file,pls click here.

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

hI @v-kelly-msft THANKS SO MUCH FOR THIS! I'm happy for still learning from people like you.

 

For future checkers is importan to say, is not necessary any relatio between the tables.

 

THANKS AND REGARDS!!!

camargos88
Community Champion
Community Champion

Hi @kevderbeste ,

 

What are you trying to do ?

Sum of 10 days before the date for each date in chart ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



hi @camargos88 , thats right, sum for each day, always showing 10 days back to selected day in date. 

 

thanks!

@kevderbeste ,

 

Do you want to select a date using slicer and show 10 days back ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.