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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
swwong1
Helper III
Helper III

Last X days sales revenue

Hi,

 

Regarding the above, I have seen several related posts but I was wondering if I could edit the DAX formula so it only counts after 10 days from the beginning.

 

As an example, the first value would only be from 10/1/1997, all previous days have less than 10 days in the revenue so I would want it blank.

 

Additionally, when I put this on the matrix, it seems like it attaches a value to a blank date (first row), could I get rid of it so the matrix table only shows 1/1/1997 as the first date value row?

 

Thanks!

 

Capture4.PNG

1 ACCEPTED SOLUTION

Hi,

 

You may dowload my PBI file from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @swwong1,

 

In my test, there is no blank date shown in visual. To help reproduce the same scenario, please provide some sample data.

1.PNG

 


 As an example, the first value would only be from 10/1/1997, all previous days have less than 10 days in the revenue so I would want it blank.

 

Additionally, when I put this on the matrix, it seems like it attaches a value to a blank date (first row), could I get rid of it so the matrix table only shows 1/1/1997 as the first date value row?

I am concern whether you want the first date to be 10/1/1997 or 1/1/1997. Please show us your desired result. Besides, to get rid of the blank value, is it possible to filter it out via applying "visual level filter"?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yulgu-msft

 

Thanks for the below, let me prepare some sample data so you can assist why there is a blank date.

 

For my desired result, please see below:

          Since this is a 10-day rolling revenue, it is not actually a 10-day rolling until the first 10 days.

 

Capture6.PNG

 

 

@swwong1

 

I tried with the coding below and it started to show the data with 1/10/2017. I am not sure if you want to see the date from 1/1 to 1/9 with blank value. 

 

Measure =
VAR MINDATE =MINX(ALL('test'),'test'[date])
RETURN

IF(DATEDIFF(MINDATE,SELECTEDVALUE('test'[date]),DAY)<9,BLANK(),CALCULATE(SUM(test[amount]),DATESINPERIOD('date'[Date],LASTDATE('date'[Date]),-10,DAY)))
 
screenshot.JPG
Hope this is helpful.
 
Thanks and BR
Ryan
 




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

Proud to be a Super User!




Hi @ryan_mayu

 

Thanks for this.

 

Is it possible to still show the dates from 1/1 to 1/9 as blank?

 

Hi,

 

You may dowload my PBI file from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

Many thanks for the file, 

swwong1
Helper III
Helper III

I have used the report filter to remove the blank date but is it a default behaviour to create a blank date before the first date?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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