Hi, this video has really been helpful. Thank you so much! If I changed this visual from a column chart to a matrix how would I remove all the months that are appearing with zero values?
Hi very useful video, I have implemented using your logic, my question is how can we replicate this with the week such as last n weeks in the period, and your dax measure has interval as month but it doesn't support week
All are great; please, if you could help me. I like to have the last 3 previous months (based on the current day, but without the current month) and no slicer.
Hi! Thank you for this video. It’s very easy to follow. But somehow, whenever I select a Month from the slicer, the chart will show zero values for the month(s) prior to that Month selected in the slicer. I can’t understand why. Can you help out? Thanks!
Glad to know it is helpful. If you follow along, make sure you are not missing any step.also check out your Power BI Data model for any unwanted automatic relationships. if everything is right it should work same way as it works in the demo.
Perfect example, very helpful! Similar doubt here. It works well but I only see those periods with data (bars for periods with no data are hided). If i want to see those hided bars i can show blank data (for X axis, period) but then i see all calendar periods, not only the last n. Any idea to see only the last N even if they do not have data? Thanks
Glad it was helpful! For this you need to add additional validation in your expression, which will also check if COUNTROWS() in that particular scenario is blank or not including the date ranges to fall within previous N months.
The data you have in the video is enough. Simply erase one month sales data from the datasource and select a period that includes that month. Cause is blank, It will not appear in the axis, neither in bars, of course. The visual effect is that you have selected n-1 periods. If you want to see it you can show blank data for the x axis but then you see all the periods of the secondary dates, not only the selected ones. One solution can be put 0 instead of blank but i've not realized how to do it only for the sales for the selected period. When i try it, all periods, even if they are not in the selected period, with no data appears as 0, so they also appears in the x axis. Thanks so much for you help
@@Robert-qn6tt Store the final result in a variable called _result and then return with below expression- IF( SELECTEDVALUE('new Dim Date'[Year-Month]) IN CALCULATETABLE( VALUES('new Dim Date'[Year-Month]), 'new Dim Date'[Date] in _Periodshift ) && _result = BLANK(), 0, _result )
Glad to know it was helpful to you. This is dynamic Last N month not last 6 months. and yes, with little changes you can have all previous day as well.
To have such sequence, you can either sort the year month column by an additional sort helper column of year month with integer value e.g. 202310 for Oct 2023. Or you can duplicate the date column and the change the format to Year month
Hi @PowerBIHelpline, Thanks for this video. Really helps! Can you please help me with on how to get last 3/6/12 closed months including the current month to date? (Eg: If we are on 26th Dec and if I want last 3 months then I want to show Sep, Oct, Nov all full months + Dec till 26th)
Glad to know it is helpful to you. I personally like such period comparison, and have posted multiple videos on these topic. I think you should check this video "CUSTOM PERIOD SLICER IN POWER BI"
I have posted a video, how to show previous year single column, and then followed by monthly columns for current year, and in last a single column for Budget. you can use the techniques from that video, along with this.
You can apply a sort order. We have also posted a video for Past N or future N months based on selected measure, you can check it out. Sort order is also taken care in that.
![[FINALE] 💸ที่แท้พ่อเป็นผู้ถือหุ้นบริษัทพันล้าน เขากลับใจตอนนี้ก็สายไปแล้ว🙇🏻♂️#ละครคุณธรรม #short](http://i.ytimg.com/vi/yFwgjTFISUI/mqdefault.jpg)
Thanks soooooo much! I was blocked when working on very important project for the company!
Good Job!
Glad I could help!
i loved it, it helped me a lot, thanks, I was just blocked and this video put me on the rail again , thanks
Glad it helped! Thanks for watching.
Hi, this video has really been helpful. Thank you so much! If I changed this visual from a column chart to a matrix how would I remove all the months that are appearing with zero values?
Glad to know it is so useful to you. you can do so by replacing the 0 values with blank inside your DAX expressions. depending on the visual's need.
@@PowerBIHelpline That did the trick! Thank you so much.
Hi very useful video, I have implemented using your logic, my question is how can we replicate this with the week such as last n weeks in the period, and your dax measure has interval as month but it doesn't support week
This video helped me a lot, Thank you.
Glad to hear it!
All are great; please, if you could help me. I like to have the last 3 previous months (based on the current day, but without the current month) and no slicer.
The last 3 previous, like now, is Apr. 2023. I want Jan 2023, Feb 2023, and Mar 2023 sales; there is no slicer. Could you help?
How can I do the conditional formatting to give green color alone for the month with highest no of sales?
Thanks for watching. We have posted videos about conditional highlights including the highest/lowest values please checkout the playlist.
Hi! Thank you for this video. It’s very easy to follow. But somehow, whenever I select a Month from the slicer, the chart will show zero values for the month(s) prior to that Month selected in the slicer. I can’t understand why. Can you help out? Thanks!
Glad to know it is helpful. If you follow along, make sure you are not missing any step.also check out your Power BI Data model for any unwanted automatic relationships. if everything is right it should work same way as it works in the demo.
Perfect example, very helpful! Similar doubt here. It works well but I only see those periods with data (bars for periods with no data are hided). If i want to see those hided bars i can show blank data (for X axis, period) but then i see all calendar periods, not only the last n.
Any idea to see only the last N even if they do not have data?
Thanks
Glad it was helpful!
For this you need to add additional validation in your expression, which will also check if COUNTROWS() in that particular scenario is blank or not including the date ranges to fall within previous N months.
Thanks! Could you please provide a simple example of this validation? Thanks a lot!@@PowerBIHelpline
You can send an email by mentioning the detailed requirement, with some sample/dummy dataset.
The data you have in the video is enough. Simply erase one month sales data from the datasource and select a period that includes that month. Cause is blank, It will not appear in the axis, neither in bars, of course. The visual effect is that you have selected n-1 periods. If you want to see it you can show blank data for the x axis but then you see all the periods of the secondary dates, not only the selected ones.
One solution can be put 0 instead of blank but i've not realized how to do it only for the sales for the selected period. When i try it, all periods, even if they are not in the selected period, with no data appears as 0, so they also appears in the x axis.
Thanks so much for you help
@@Robert-qn6tt
Store the final result in a variable called _result and then return with below expression-
IF(
SELECTEDVALUE('new Dim Date'[Year-Month]) IN
CALCULATETABLE(
VALUES('new Dim Date'[Year-Month]),
'new Dim Date'[Date] in _Periodshift
) && _result = BLANK(),
0, _result
)
pls did you create the dimdate table from the sales table then duplicated it?
Yes, You can create a date table in Power query as well
Thank you!!! This video really helped me. Instead of the last 6 months could it select all previous dates?
Glad to know it was helpful to you. This is dynamic Last N month not last 6 months. and yes, with little changes you can have all previous day as well.
Thank you! 3x ❤❤❤❤😊😊😊😊
Thank you so much
Hi, thank you for the video. May I know how to have the sequence of the Year-Month in the bar chart accordingly?
To have such sequence, you can either sort the year month column by an additional sort helper column of year month with integer value e.g. 202310 for Oct 2023. Or you can duplicate the date column and the change the format to Year month
Hi @PowerBIHelpline, Thanks for this video. Really helps! Can you please help me with on how to get last 3/6/12 closed months including the current month to date? (Eg: If we are on 26th Dec and if I want last 3 months then I want to show Sep, Oct, Nov all full months + Dec till 26th)
Glad to know it is helpful to you.
I personally like such period comparison, and have posted multiple videos on these topic. I think you should check this video "CUSTOM PERIOD SLICER IN POWER BI"
how can you also include a category slicer to this?
Yes you can
Are you able to show or explain how you could have this functionality work with a Prior year bar also?
I have posted a video, how to show previous year single column, and then followed by monthly columns for current year, and in last a single column for Budget. you can use the techniques from that video, along with this.
Hi, it's informative 👍. Can we do the same with the fiscal calendar as well?
Yes you can!
what is the formula used in the Period Column
there are many ways to get this,
1- Concat Year and month name column
2- change the format of date column to yyyy- mmm
Why do you make the connection between two tables "one to many"? i guess it is wrong as we have only distinct values in both columns!
Yes, That doesn't matter in this case.
Hi, Thank you for this video. Have also tried doing the same I'm getting column total wrong for the last 4 months.
If it is only for the last 4 months, then you should checkout the expressions.
@@PowerBIHelpline Thanks for the reply. Have checked the expression and I'm using dynamic slicer to check -1% of moving or 5% of moving average.
Thx, this is perfect.
Thanks for watching. Cheers!
Hi I want to select last month data dyanamically in report how can I do
Recently i posted a video on this topic to select latest month dynamically in Power BI slicer. check that out.
You are amazing
Thanks for watching
Nice
Thanks
Hi interesting tutorial, love it! Could you provide the pbix file to download and let us see the detail?
Thanks for watching. To get the PBIX file, you can send an email by mentioning the video title as email subject.
How to count Average sales in last 6 months ?
If you want to have a rolling average. you can use DATESINPERIOD() DAX function
Can you give me that file again? thank you
I need PBIX file
How to download it?
Thanks for watching. To get the PBIX file, you can send an email by mentioning the video title as email subject.
i see that the months don't sorted well
You can apply a sort order. We have also posted a video for Past N or future N months based on selected measure, you can check it out. Sort order is also taken care in that.