Forum Discussion
DATE FILTER WITH LABEL
Hi All,
I am new to Power BI and just started learning the basics. However, I have a date filter requirement for my dashboard as below. Please help me in understanding it from basic on how to achieve it.
I have around 10 tables used in Power BI for the visualization which are extracted from SQL server. I want to add a date filter data for visualization based on "past 7 days", "past one month", "past one year" & custom selected date range. I want to use "SESSION_START_DATE" column (I have duplicated the column which had time as well and converted data type to 'date') from "TBL_LOG_SESSION" for filtration.
I watched a video which exactly matches my requirement but, it's not working when I tried to implement it. Here goes the link for that video -
Thank you all in advance,
Shreya
5 Replies
Anonymous , Hope you have created and date table and joined it with all the tables on date column (no time stamp)
But if you select 1 date and want more than that you need independent date table
//Date1 is independent Date table, Date is joined with Table
Last 12 month =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))//Date1 is independent Date table, Date is joined with Table
last 7 days =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -7
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))//Date1 is independent Date table, Date is joined with Table
Last year =
var _max = year(maxx(allselected(Date1),Date1[Date]))-1,12,31)
var _min = date(year(_max),1,1)
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Anonymous , Here I am proposing two date tables, one which is joined with required facts. And one does not join. How many joins will depend on number of fact you have
Please refer to this video
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
Hi Anonymous ,
Have you solved your issue now? If so, please share your way to us so that can help more people.
If not, please check the relationship between the tables.
I have also found a similar post, please refer to it to see if it helps you.
DAX: Creating labels with FIRSTDATE and a date filter or dropdown
- Setting up your model with a Valuation fact table, and Asset, Date, and Status dimension tables.
- The Asset table contains each Object ID along with its FirstDate
- FirstDate is derived in Power Query, by grouping the original Valuation table by Object ID and calculating the minimum Valuation Date.
- The Date table contains appropriate date columns (including quarter identifiers).
- The Status table is a simple dimension table for Status.
- The Valuation table contains the columns you posted above, plus a Status column derived in Power Query.
- Status is found by joining the raw Valuation data with Asset, and conditionally setting status based on whether Valuation Date = FirstDate.
- Since FirstDate is a column of the Asset dimension, and Status is a column in the Status dimension, they can easily be included in any visuals, without needing any complicated DAX.
If it is possible, please provide your pbix without privacy information and desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
In the video you shared, I can see the measure created to calculate sum of sales. I don't have any column with numbers so I took unique ID (U_NO) column for adding SUM, hope that works.
After creating measures and tables as suggested in the video, I am getting blank column for "Last 12 Months", please suggest what went wrong.
