Forum Discussion
Help with chart and filters
Anonymous
First, unpivot the table
https://radacad.com/pivot-and-unpivot-with-power-bi
from month Create a date
Date = "1-" & table[Month] based on you format
Join with a date calendar table
There you can create month year and month year sort column
Month Year = format(date[date],"MMM-YYYY")
Month Year Sort = format(date[date],"YYYYMM")
Use those
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Anonymous
First, unpivot the table
https://radacad.com/pivot-and-unpivot-with-power-bi
from month Create a date
Date = "1-" & table[Month] based on you format
Join with a date calendar table
There you can create month year and month year sort column
Month Year = format(date[date],"MMM-YYYY")
Month Year Sort = format(date[date],"YYYYMM")
Use those
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
hello amitchandak
Thanks for the unpivot solution that was great.Now i get teh products in a slicer.Then i used the below
year month new = 'Test forlder'[M-Y].[Year]&'Test forlder'[M-Y].[MonthNo] to get the year and mont in YYYYMM value but then in teh slicer i dont get a between just a list and dropdown.can this be solved.Will try out teh date solution as well but then 'Date = "1-" & table[Month] based on you format' what will this return if an example could be shown.when itried it gave 1-1 for january but then i guess there is more to it.ThanksIf you make YYYYMM datatype as a number then you can get between. But you may not be able to control value. Like it might allow 202013.
Anonymous , Hope I got it correctly ?
Hello amitchandak
Yes when i convert it to whole numnber then i se the issue the slicer can get any number in between 20101(say january) to 202012(2020 dec) like 188477.Could you please explain on hte date solution then,maybe i need to get that in.
Thanks