Show previous 6 months, dynamic

[anupampandey]

Hi,

I need to show the previous 6 month on line chart plot.

Eg: If I select year 2017 and month Apr than it should show me data from Apr, Mar, Feb, Jan, Dec, Nov. Similarly if I select year 2016 and month Sep than I need to see Sep, Aug, Jul, Jun, May, Apr of that year. It should not depend on year from slicer.

I have tried doing but not getting the desired result.

Also would like to compute Previous year Same period (month) sales.

Eg: If I am in month of Apr and MTD and having sales total from 1st Apr'17 to 3rd Apr'17 would like to see the total from 1st Apr'16 to 3rd Apr'16 only.

Please help me above 2 issues.

Regards,

Anupam 

[anupampandey]

Hi,

I am out of luck to do the same. Anyone can help me on same.

Awaiting solution

Thanks,

Anupam

[v-shex-msft]

Hi @anupampandey,

Can you please share some sample data to test?

Regards,

Xiaoxin Sheng

[anupampandey]

Hi @v-shex-msft

You can download the pbix file "https://1drv.ms/f/s!AlBszrYdSOY2lscS6rez9r9qkVRtPw".

Regards,

Anupam

[v-shex-msft]

Hi @anupampandey,

I modify the formula of "last 6 month", current it can dynamic calculate the last 6 month based on "today" function.

Last6Months = 
var currDate=TODAY()
return 
IF (AND ([DateKey] >= DATE(YEAR(currDate),MONTH(currDate)-6,DAY(currDate)),[DateKey]<=currDate),1,0)

>>>>Also would like to compute Previous year Same period (month) sales.

You formula seems works, I drag these column to a matrix and it can get the correct value from previous year.

Regards,

Xiaoxin Sheng

[anupampandey]

Hi @v-shex-msft,

Thanks for input.

One last things would like to know, is there a way to control the same based on the month selection. Suppose if I selected Year 2017 and month Apr from slicer can it show Apr, Mar, Feb, Jan, Dec, Nov. If this can be possible than it would be a best to show on line chart.

Thanks once again.

Regards,

Anupam

[v-shex-msft]

Hi @anupampandey,

>>Nov. If this can be possible than it would be a best to show on line chart.

Current line chart seems not support to use legend and multiple value at same time.

You can vote below ideas which about use legend and multiple value field in line chart:

Line Chart with multiple values fields and legends

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @anupampandey,

>>Eg: If I select year 2017 and month Apr than it should show me data from Apr, Mar, Feb, Jan, Dec, Nov. Similarly if I select year 2016 and month Sep than I need to see Sep, Aug, Jul, Jun, May, Apr of that year. It should not depend on year from slicer.

Based on test, power bi not support the dynamci filter pervious range, you need to manual seting the date slicer.

>>Also would like to compute Previous year Same period (month) sales.

If you want to calculate the running total based on the choose of slicer, you can try to use below formula.

Running Total = 
var selected= IF(HASONEVALUE('Table'[Date]),VALUES('Table'[Date]),BLANK())
return
SUMX(FILTER(ALL(TableB),TableB[Date]>=DATE(YEAR(selected),MONTH(selected)-6,DAY(selected)&&TableB[Date]<=selected)),[Fiscal Date])

Regards,

Xiaoxin Sheng

[anupampandey]

Hi,

I am trying your method but not successed.

I hope "Table" is calender table, "TableB" is sales fact table but what date is [Fiscal Date].

Could you explain same with example?

Regards,

Anupam 

[v-shex-msft]

Hi @anupampandey,

>>I hope "Table" is calender table, "TableB" is sales fact table but what date is [Fiscal Date].

"Table" is the calender table, "Table B" is the fact table, "[Fiscal Date]" means the value column(just the wrong paste).

These tables not need to create the relationship, drag the measure to the visual with fact table data then create a slicer with calender date and select one date.

Regards,

Xiaoxin Sheng