Forum Discussion

manoj_0911's avatar
manoj_0911
Icon for Kudo Commander rankKudo Commander
2 years ago
Solved

Assistance Needed: Creating a Static Trend Chart for Last Six Months in Power BI

Hello Power BI Community,

 

I'm currently working on building a trend chart in Power BI that displays billed_users_data for the last six months. I'm seeking guidance on creating a trend chart with specific criteria:

 

- The chart should display data for the last six months only.
- The six-month duration should align with the month selected in the month parameter. For example, if the month selected is December, then the trend should include December, November, October, September, August, and July.
- The trend chart should remain static, reflecting the same six-month period regardless of date changes.
- I aim to visualize trends over this fixed time frame using a line chart or preferred visualization method.

 

I'm currently working on converting Tableau reports to Power BI, and these are the tableau calculations i used to achieve my desired output.

 

1. Filter: 6 Selected Months = DATEDIFF('month',[Date Truncated to Month],[Selected Month]) < 6
AND
DATEDIFF('month',[Date Truncated to Month],[Selected Month]) >= 0

2. Date Truncated to Month = DATETRUNC('month',[Report Month])

3. Selected Month = DATE(str([YEAR]) +"-"+ STR([MONTH]) +"-01")

4. Filter: Month and Year = DATE(STR([YEAR]) + "-" + STR([MONTH]) + "-01")
= [Date for Filter]

5. Date for Filter = DATE(DATETRUNC('month', [Report Month]))

 

Could someone please provide guidance or steps on how to replicate this functionality in Power BI? I'm specifically looking to create a static trend chart for the last six months, aligned with the selected month parameter, similar to the approach in Tableau.

 

Thank you in advance for your assistance!

https://drive.google.com/file/d/1fzfck3WHfn7xLh0OrHTEXg0wpaiDs94S/view?usp=sharing 

 

Best regards,
Manoj Prabhakar

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi manoj_0911 ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create a slicer table and a measure. Note that this slicer table has no model relationship with the source table.

    SlicerTable = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))
    Flag = 
var _year=SELECTEDVALUE('SlicerTable'[Date].[Year])
var _month=SELECTEDVALUE(SlicerTable[Date].[MonthNo])
var _date1=EOMONTH(DATE(_year,_month,1),-6)+1
var _date2=EOMONTH(DATE(_year,_month,1),0)
RETURN IF(MAX('Table'[date])<=_date2 && MAX('Table'[date]) >= _date1,1,0)

    (3) Place [Flag=1] on the visual object screening and then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    manoj_0911 ,

    1. Filter: 6 Selected Months = DATEDIFF('month',[Date Truncated to Month],[Selected Month]) < 6
    AND
    DATEDIFF('month',[Date Truncated to Month],[Selected Month]) >= 0

     

    If date is selected and then you need 6 months, you need a disconnected table, if the date is not filtered then you can simply filter

     

    //Date1 is independent Date table, Date is joined with Table
    new measure =
    var _max = maxx(allselected(Date1),Date1[Date])
    var _min = eomonth(_max, -12) +1
    return
    calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

    //Last 12 based on today
    new measure =
    var _max = eomonth(today(),0)
    var _min = eomonth(today(),-12)+1
    return
    calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

     

     

    2. Date Truncated to Month = DATETRUNC('month',[Report Month])

    Month Start Date = eomonth([Date], -1) +1

     

     

    3. Selected Month = DATE(str([YEAR]) +"-"+ STR([MONTH]) +"-01")

    selectedvalue(Date[Year]) & "-" &selectedvalue(Date[Month]) & "-01"

    4. Filter: Month and Year = DATE(STR([YEAR]) + "-" + STR([MONTH]) + "-01")

     

    selectedvalue(Date[Year]) & "-" &selectedvalue(Date[Month]) & "-01"
    = [Date for Filter]

    5. Date for Filter = DATE(DATETRUNC('month', [Report Month]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi manoj_0911 ,

     

    According to your description, here are my steps you can follow as a solution.

    (1) This is my test data. 

    (2) We can create a slicer table and a measure. Note that this slicer table has no model relationship with the source table.

    SlicerTable = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))
    Flag = 
var _year=SELECTEDVALUE('SlicerTable'[Date].[Year])
var _month=SELECTEDVALUE(SlicerTable[Date].[MonthNo])
var _date1=EOMONTH(DATE(_year,_month,1),-6)+1
var _date2=EOMONTH(DATE(_year,_month,1),0)
RETURN IF(MAX('Table'[date])<=_date2 && MAX('Table'[date]) >= _date1,1,0)

    (3) Place [Flag=1] on the visual object screening and then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.