Last 10 days stock in a line chart that changes dynamically with the month filter

[Anonymous]

Hi all,

I have to show a trend chart of last 10 days of stock that should change with selection of the month filter.

For eg: if july is selected then i have to show the last 10 days of july's stock data.

Can this be done?

[amitchandak]

For me it worked like this

Sales 10 Days = (
VAR _Cuur_start = Max('Compare Date'[Compare Date]) -10
VAR _Curr_END = Max('Compare Date'[Compare Date])
return
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <= _Curr_END )
)

[Anonymous]

Hi @amitchandak 

Not working,

I have to make it work on a line chart that shows the 10 days on X axis .

[amitchandak]

Some thing like this ?. "Sales 10 days" is on last 10 day and sales is not.

[Anonymous]

@amitchandak 

Yes like this. But I have a month filter and not a date filter. I have to show the last 10 days of every month.

[amitchandak]

Try this. Slicer Should not join with fact and Use Date from Fact on X-Axis. Hope that is possible in your case.

[Osmanakgunduz]

Hello Payal4;

can you create a calculated column in your date table like this? after that, use this measure as a filter and select "1". it will filter the last 10 days of the month.

measure= IF((DimDate[DayNumberOfMonth])-(MAX(DimDate[DayNumberOfMonth])-10)>0;1;0)

[Anonymous]

Hi @Osmanakgunduz

What calculated column should we used?

[Osmanakgunduz]

Hello @Anonymous 

as I mentioned, the formula is:

Column= IF((DimDate[DayNumberOfMonth])-(MAX(DimDate[DayNumberOfMonth])-10)>0;1;0)

Best Regards.

[Osmanakgunduz]

Hello Payal4,

in addition, to use this formula, you must have a column in your date table that contains day number of the month.

[Anonymous]

Hi @Osmanakgunduz 

Is there a way to do this without the day number of year as I do not have the usual calender and have 445 calender? I would have to create a column with day number of year that would again require some logic. I have less time to do the same.

Please help

[Osmanakgunduz]

hello @Anonymous 

can you add your date table's print screen?

[Anonymous]

@Osmanakgunduz will not be able to share that.

But as an eg: july 2019 starts with 29th june and ends on 26th july

[Osmanakgunduz]

I want to see your date table data, table's columns.

 

[Anonymous]

[amitchandak]

Please check this https://medium.com/chandakamit/power-bi-comparing-data-across-date-ranges-36be49b68613.

The days formula code I given in last reply.

Data Screenshots

[Anonymous]

I will not be able to remove the link between the two tables unfortunately as I have other charts too on that page.

[amitchandak]

I think you can remove it one formula. Check the solution here https://community.powerbi.com/t5/Desktop/Deactivate-Relationship-in-a-measure/m-p/454772#M210708

In calculate you can make it crossfilter

,
    CROSSFILTER ( AH[NextChangedDate], _dtDateFilter[Date filter], NONE )

[amitchandak]

Have you got the solution?

[Anonymous]

@Osmanakgunduz 

I do no have a gregarion calender. Hence, need to calculate the day number of month.

[tex628]

I would do something like this,

Measure =
VAR maxDate = MAX(Calendar[Date])
VAR maxMonth = MONTH(maxDate) 
VAR minDate = maxDate -10
Return
CALCULATE(
[Amount],
All(calendar),
Calendar[Date]=<maxDate,
Calendar[Date]=>minDate,
Calendar[Month]=maxMonth
)