Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply

Display the selected period in the slice for all years

Hi community,

I'm having the following case:

For example, in a date Slicer, I select the period 10/01/2019 - 12/01/2019 . Then, I need to display a bar chart with same
period selected for all years of my dataset.

Example:
I have this data set:

YearMonthTotal
2018jan50
2018fev22
2018mar11
2018abr16
2018mai23
2018jun32
2018jul25
2018ago11
2018set554
2018out5
2018nov10
2018dez15
2019jan12
2019fev21
2019mar19
2019abr27
2019mai226
2019jun222
2019jul33
2019ago24
2019set99
2019out10
2019nov10
2019dez20


I need that my result be like the image below:
grafico mes outubro.jpg
The Slicer need be a slider format.

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Hi @Wilianfilho_ps - believe it or not, you do not need any measures for this. You can use an Implict Measure by just dragging in the field, but it does require some creative modeling, and it has one assumption, below:

  1. Create your date table manually. I recommend using this date table, because it is awesome. 😂👍 There are instructions on how to copy and paste that in to Power Query so it works.
  2. Change the Source row to be whatever dates you want. It starts with Jan 1, 2015 through Dec 31, 2020. I changed it in the attached PBIX to Jan 1, 2018 through Dec 31, 2019 to correspond with your data.
  3. Add a new column to that Date Table called "Month Day" by adding a custom column in Power Query. It needs to be this formula:
    1. #date(List.Max(#"Added MonthDay"[Year]), [Month], [Day])
    2. Set the data type as a date
    3. This is the assumption - your date slicer will always be for the current year's dates, but will show all related years.
  4. Close and load
  5. Mark this date table as a date table.
  6. Add your date slicer, and use this new Month Date field. It should show Jan 1, 2019 through Dec 31, 2019. (I changed the source line of the date table to be 2018 and 2019 per your supplied data.
  7. Create a bar chart.
    1. Add a simple measure of SUM(Table[Total]) or just drag the total to it.
    2. Add the Year field from the date table to the Axis

SlicerWithAllYears.gif

Here is a link to my PBIX file that you can look at. It is easier when you can see the tables, the model, and the source Power Query data.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

@Wilianfilho_ps - I'm marking this as solved. I hope it helped. If not, please reply back and let us know.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

Hi @Wilianfilho_ps - believe it or not, you do not need any measures for this. You can use an Implict Measure by just dragging in the field, but it does require some creative modeling, and it has one assumption, below:

  1. Create your date table manually. I recommend using this date table, because it is awesome. 😂👍 There are instructions on how to copy and paste that in to Power Query so it works.
  2. Change the Source row to be whatever dates you want. It starts with Jan 1, 2015 through Dec 31, 2020. I changed it in the attached PBIX to Jan 1, 2018 through Dec 31, 2019 to correspond with your data.
  3. Add a new column to that Date Table called "Month Day" by adding a custom column in Power Query. It needs to be this formula:
    1. #date(List.Max(#"Added MonthDay"[Year]), [Month], [Day])
    2. Set the data type as a date
    3. This is the assumption - your date slicer will always be for the current year's dates, but will show all related years.
  4. Close and load
  5. Mark this date table as a date table.
  6. Add your date slicer, and use this new Month Date field. It should show Jan 1, 2019 through Dec 31, 2019. (I changed the source line of the date table to be 2018 and 2019 per your supplied data.
  7. Create a bar chart.
    1. Add a simple measure of SUM(Table[Total]) or just drag the total to it.
    2. Add the Year field from the date table to the Axis

SlicerWithAllYears.gif

Here is a link to my PBIX file that you can look at. It is easier when you can see the tables, the model, and the source Power Query data.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@Wilianfilho_ps - have you had a chance to review either of these solutions? If you always have just 2 years of data, @amitchandak's time intelligence is a bit easier to understand and will work.

 

If you have multiple years, my model change will compensate for that but needs a little more setup to work.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
amitchandak
Super User
Super User

@Wilianfilho_ps , With a date table, You can use Trailing year measure

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

or you can use SAMEPERIODLASTYEAR

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors