Forum Discussion

anupampandey's avatar
anupampandey
Icon for Helper III rankHelper III
9 years ago

Show previous 6 months, dynamic

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 

 

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      anupampandey
      Icon for Helper III rankHelper III
      9 years ago

      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 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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

  • anupampandey's avatar
    anupampandey
    Icon for Helper III rankHelper III
    9 years ago

    Hi,

     

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

    Awaiting solution

     

     

    Thanks,

    Anupam