Last X days sales revenue

[swwong1]

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!

[v-yulgu-msft]

Hi @swwong1,

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

---

 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

[swwong1]

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.

[ryan_mayu]

@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)))

Hope this is helpful.

Thanks and BR

Ryan

[swwong1]

Hi @ryan_mayu

Thanks for this.

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

[Ashish_Mathur · Accepted solution]

Hi,

You may dowload my PBI file from here.

Hope this helps.

[swwong1]

Hi @Ashish_Mathur

Many thanks for the file, 

[swwong1]

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?